﻿using System;
using System.Data;
using System.Web;
using Security;
using DAL;
using Config;
using System.Collections;
using System.IO;
using System.Collections.Generic;
using System.Net;
using System.Xml;
using ImageResizer;
using ImageResizer.Encoding;

namespace Credits
{
    public class Module
    {
        public static readonly string ID = "Module Credits";
        public static readonly string Description = "Creating And Processing Credits (Module Credits)";
        public static readonly string DBConnectionsStringKey = "creditsDBConnectionString";
        private static readonly string RootFilesDirectoryKey = "RootFilesDirectory";
        private static readonly string ModuleCreditsFolderKey = "ModuleCreditsFolder";
        private static readonly string ModuleCreditsTrainigsFolder = "ModuleCreditsTrainigsFolder";

        public static readonly string ConsultEvalPhotoFolderName = "ConsultEvalPhoto";
        public static readonly string ConsultEvalPhotoFolderLargeName = ConsultEvalPhotoFolderName + "/Large";
        public static readonly string ConsultEvalPhotoFolderTumbsName = ConsultEvalPhotoFolderName + "/Tumbs";
        public static readonly string ContractsFolderName = "Contracts";
        public static readonly string PersonalDocumentsFolderName = "PersonalDocuments";


        string mRootModuleCreditFolder = Config.ConfigManager.GetFileDirectory(RootFilesDirectoryKey) + Config.ConfigManager.GetFileDirectory(ModuleCreditsFolderKey);
        string mTrainigsFolder = Config.ConfigManager.GetFileDirectory(RootFilesDirectoryKey) + Config.ConfigManager.GetFileDirectory(ModuleCreditsFolderKey) + Config.ConfigManager.GetFileDirectory(ModuleCreditsTrainigsFolder);

        DataBridge mDataBridge = null;

        public static void Register()
        {
            Registrar.RegisterModule(ID, Description);
            Domains.InputClientPersonalData.Register();
            Domains.ManageLoans.Register();
            Domains.ManageLoanProducts.Register();
            Domains.ManageConsultEval.Register();
            Domains.ApprovalLoans.Register();
            Domains.Reports.Register();
        }

        private string mLastError = string.Empty;
        private string Plus = string.Empty;

        public string LastError
        {
            get
            {
                return mLastError;
            }
        }

        public string RootModuleFolder
        {
            get
            {
                return mRootModuleCreditFolder;
            }
        }

        public string TrainigsFolder
        {
            get
            {
                return mTrainigsFolder;
            }
        }

        public Module()
        {
            mDataBridge = new DataBridge(ConfigManager.GetDbConnectionString(Module.DBConnectionsStringKey), ConfigManager.GetProviderName(Module.DBConnectionsStringKey));
            Plus = mDataBridge.ConcatSimbol + " ' ' " + mDataBridge.ConcatSimbol;
        }

        #region Client Region

        public bool AddNewClient(Credits.DataObjects.Client clientObject, out int newClientID)
        {
            DateTime EmptyDate = DateTime.MinValue;
            newClientID = 0;

            bool result = false;
            try
            {
                if (clientObject != null)
                {
                    string nonQuery = "INSERT INTO Client (FirstName,LastName, DateOfBirth, PlaceOfBirth, personalID, gender, buletinSeria, dataEliberarii, dataExpirarii, eliberatDe, civilStatus, HusbandID, church, numberOfChildren, socialStatus, studyLevel, imageURL, telefonFix, telefonMobil, telefonMobil2, viza_country, viza_raion, viza_urbanRural, viza_localitatea, viza_stradaAdresa, raddress_country, raddress_raion, raddress_urbanRural, raddress_localitatea, raddress_stradaAdresa, email, venitFamInceputAfacere, nrPersoaneIntretinere, situatiaMateriala, slujireInBiserica, pastorLider, telefonPastor, personalfolder, bank_id, contbancar, sortGroup) "
                                + " OUTPUT INSERTED.ClientID "
                                + " VALUES (@FirstName, @LastName, " + (clientObject.BirthDate.Equals(EmptyDate) ? "NULL" : "@DateOfBirth") + ", @PlaceOfBirth, @personalID, @gender, @buletinSeria, " + (clientObject.DataEliberariiBuletin.Equals(EmptyDate) ? "NULL" : "@dataEliberarii") + ", " + (clientObject.DataExpirariiBuletin.Equals(EmptyDate) ? "NULL" : "@dataExpirarii") + ", @eliberatDe, @civilStatus, @HusbandID, @church, @numberOfChildren, @socialStatus, @studyLevel, @imageURL, @telefonFix, @telefonMobil, @telefonMobil2, @viza_country, @viza_raion, @viza_urbanRural, @viza_localitatea, @viza_stradaAdresa, @raddress_country, @raddress_raion, @raddress_urbanRural, @raddress_localitatea, @raddress_stradaAdresa, @email, @venitFamInceputAfacere, @nrPersoaneIntretinere, @situatiaMateriala, @slujireInBiserica, @pastorLider, @telefonPastor, @personalfolder, @bank_id, @contbancar, @sortGroup ); ";

                    Hashtable parameters = new Hashtable();
                    parameters.Add("@FirstName", clientObject.FirstName);
                    parameters.Add("@LastName", clientObject.LastName);
                    if (!clientObject.BirthDate.Equals(EmptyDate)) parameters.Add("@DateOfBirth", clientObject.BirthDate);
                    parameters.Add("@PlaceOfBirth", clientObject.PlaceOfBirth);
                    parameters.Add("@personalID", clientObject.PersonalID);
                    parameters.Add("@gender", clientObject.Gender);
                    parameters.Add("@buletinSeria", clientObject.BuletinSeria);
                    if (!clientObject.DataEliberariiBuletin.Equals(EmptyDate)) parameters.Add("@dataEliberarii", clientObject.DataEliberariiBuletin);
                    if (!clientObject.DataExpirariiBuletin.Equals(EmptyDate)) parameters.Add("@dataExpirarii", clientObject.DataExpirariiBuletin);
                    parameters.Add("@eliberatDe", clientObject.BuletinEliberatDe);
                    parameters.Add("@civilStatus", clientObject.CivilStatus);
                    parameters.Add("@HusbandID", clientObject.HusbandID);
                    parameters.Add("@church", clientObject.Church);
                    parameters.Add("@numberOfChildren", clientObject.NumberOfChildren);
                    parameters.Add("@socialStatus", clientObject.SocialStatus);
                    parameters.Add("@studyLevel", clientObject.StudyLevel);
                    parameters.Add("@imageURL", clientObject.ImageURL);
                    parameters.Add("@telefonFix", clientObject.TelefonFix);
                    parameters.Add("@telefonMobil", clientObject.TelefonMobil);
                    parameters.Add("@telefonMobil2", clientObject.TelefonMobil2);
                    parameters.Add("@viza_country", clientObject.Viza_Country);
                    parameters.Add("@viza_raion", clientObject.Viza_Raion);
                    parameters.Add("@viza_urbanRural", clientObject.Viza_UrbanRural);
                    parameters.Add("@viza_localitatea", clientObject.Viza_Localitatea);
                    parameters.Add("@viza_stradaAdresa", clientObject.Viza_StradaAdresa);
                    parameters.Add("@raddress_country", clientObject.Raddress_Country);
                    parameters.Add("@raddress_raion", clientObject.Raddress_Raion);
                    parameters.Add("@raddress_urbanRural", clientObject.Raddress_UrbanRural);
                    parameters.Add("@raddress_localitatea", clientObject.Raddress_Localitatea);
                    parameters.Add("@raddress_stradaAdresa", clientObject.Raddress_StradaAdresa);
                    parameters.Add("@email", clientObject.Email);
                    parameters.Add("@venitFamInceputAfacere", clientObject.VenitFamInceputAfacere);
                    parameters.Add("@nrPersoaneIntretinere", clientObject.NrPersoaneIntretinere);
                    parameters.Add("@situatiaMateriala", clientObject.SituatiaMateriala);
                    parameters.Add("@slujireInBiserica", clientObject.SlujireInBiserica);
                    parameters.Add("@pastorLider", clientObject.PastorLider);
                    parameters.Add("@telefonPastor", clientObject.TelefonPastor);
                    parameters.Add("@personalfolder", clientObject.ClientPersonalFolder);
                    parameters.Add("@bank_id", clientObject.Bank_id);
                    parameters.Add("@contbancar", clientObject.ContBancar);
                    parameters.Add("@sortGroup", clientObject.SortGroup);

                    object insertedID = mDataBridge.ExecuteScalarQuery(nonQuery, parameters); // PG compliant
                    mLastError = mDataBridge.LastError;

                    if (insertedID != null && !insertedID.ToString().Equals(string.Empty))
                    {
                        newClientID = (int)insertedID;
                        result = true;

                        string clientPersonalFolder = newClientID.ToString();
                        UpdateClientPersonalFoldersInDB(newClientID, clientPersonalFolder);
                    }
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateClient(Credits.DataObjects.Client clientObject)
        {
            DateTime EmptyDate = DateTime.MinValue;

            bool result = false;
            try
            {
                if (clientObject != null)
                {
                    string nonQuery = "UPDATE Client \r\n "
                    + " SET  \r\n "
                    + "   FirstName = @FirstName, \r\n "
                    + "   LastName = @LastName, \r\n "
                    + "   DateOfBirth = " + (clientObject.BirthDate.Equals(EmptyDate) ? "NULL" : "@DateOfBirth") + ", \r\n "
                    + "   PlaceOfBirth = @PlaceOfBirth,\r\n "
                    + "   personalID = @personalID,\r\n "
                    + "   gender = @gender,\r\n "
                    + "   buletinSeria = @buletinSeria,\r\n "
                    + "   dataEliberarii = " + (clientObject.DataEliberariiBuletin.Equals(EmptyDate) ? "NULL" : "@dataEliberarii") + ",\r\n "
                    + "   dataExpirarii = " + (clientObject.DataExpirariiBuletin.Equals(EmptyDate) ? "NULL" : "@dataExpirarii") + ",\r\n "
                    + "   eliberatDe = @eliberatDe,\r\n "
                    + "   civilStatus = @civilStatus,\r\n "
                    + "   HusbandID = @HusbandID,\r\n "
                    + "   church = @church,\r\n "
                    + "   numberOfChildren = @numberOfChildren,\r\n "
                    + "   socialStatus = @socialStatus,\r\n "
                    + "   studyLevel = @studyLevel,\r\n "
                    + "   imageURL = @imageURL,\r\n "
                    + "   telefonFix = @telefonFix,\r\n "
                    + "   telefonMobil = @telefonMobil,\r\n "
                    + "   telefonMobil2 = @telefonMobil2,\r\n "
                    + "   viza_country = @viza_country,\r\n "
                    + "   viza_raion = @viza_raion,\r\n "
                    + "   viza_urbanRural = @viza_urbanRural,\r\n "
                    + "   viza_localitatea = @viza_localitatea,\r\n "
                    + "   viza_stradaAdresa = @viza_stradaAdresa,\r\n "
                    + "   raddress_country = @raddress_country,\r\n "
                    + "   raddress_raion = @raddress_raion,\r\n "
                    + "   raddress_urbanRural = @raddress_urbanRural,\r\n "
                    + "   raddress_localitatea = @raddress_localitatea,\r\n "
                    + "   raddress_stradaAdresa = @raddress_stradaAdresa,\r\n "
                    + "   email = @email,\r\n "
                    + "   venitFamInceputAfacere = @venitFamInceputAfacere,\r\n "
                    + "   nrPersoaneIntretinere = @nrPersoaneIntretinere,\r\n "
                    + "   situatiaMateriala = @situatiaMateriala,\r\n "
                    + "   slujireInBiserica = @slujireInBiserica,\r\n "
                    + "   pastorLider = @pastorLider,\r\n "
                    + "   telefonPastor = @telefonPastor,\r\n "
                    + "   personalfolder = @personalfolder,\r\n "
                    + "   bank_id = @bank_id,\r\n "
                    + "   contbancar = @contbancar,\r\n "
                    + "   sortGroup = @sortGroup\r\n "


                    + " WHERE \r\n "
                    + "   clientID = @clientID ";

                    Hashtable parameters = new Hashtable();
                    parameters.Add("@clientID", clientObject.ClientID);
                    parameters.Add("@FirstName", clientObject.FirstName);
                    parameters.Add("@LastName", clientObject.LastName);
                    if (!clientObject.BirthDate.Equals(EmptyDate)) parameters.Add("@DateOfBirth", clientObject.BirthDate);
                    parameters.Add("@PlaceOfBirth", clientObject.PlaceOfBirth);
                    parameters.Add("@personalID", clientObject.PersonalID);
                    parameters.Add("@gender", clientObject.Gender);
                    parameters.Add("@buletinSeria", clientObject.BuletinSeria);
                    if (!clientObject.DataEliberariiBuletin.Equals(EmptyDate)) parameters.Add("@dataEliberarii", clientObject.DataEliberariiBuletin);
                    if (!clientObject.DataExpirariiBuletin.Equals(EmptyDate)) parameters.Add("@dataExpirarii", clientObject.DataExpirariiBuletin);
                    parameters.Add("@eliberatDe", clientObject.BuletinEliberatDe);
                    parameters.Add("@civilStatus", clientObject.CivilStatus);
                    parameters.Add("@HusbandID", clientObject.HusbandID);
                    parameters.Add("@church", clientObject.Church);
                    parameters.Add("@numberOfChildren", clientObject.NumberOfChildren);
                    parameters.Add("@socialStatus", clientObject.SocialStatus);
                    parameters.Add("@studyLevel", clientObject.StudyLevel);
                    parameters.Add("@imageURL", clientObject.ImageURL);
                    parameters.Add("@telefonFix", clientObject.TelefonFix);
                    parameters.Add("@telefonMobil", clientObject.TelefonMobil);
                    parameters.Add("@telefonMobil2", clientObject.TelefonMobil2);
                    parameters.Add("@viza_country", clientObject.Viza_Country);
                    parameters.Add("@viza_raion", clientObject.Viza_Raion);
                    parameters.Add("@viza_urbanRural", clientObject.Viza_UrbanRural);
                    parameters.Add("@viza_localitatea", clientObject.Viza_Localitatea);
                    parameters.Add("@viza_stradaAdresa", clientObject.Viza_StradaAdresa);
                    parameters.Add("@raddress_country", clientObject.Raddress_Country);
                    parameters.Add("@raddress_raion", clientObject.Raddress_Raion);
                    parameters.Add("@raddress_urbanRural", clientObject.Raddress_UrbanRural);
                    parameters.Add("@raddress_localitatea", clientObject.Raddress_Localitatea);
                    parameters.Add("@raddress_stradaAdresa", clientObject.Raddress_StradaAdresa);
                    parameters.Add("@email", clientObject.Email);
                    parameters.Add("@venitFamInceputAfacere", clientObject.VenitFamInceputAfacere);
                    parameters.Add("@nrPersoaneIntretinere", clientObject.NrPersoaneIntretinere);
                    parameters.Add("@situatiaMateriala", clientObject.SituatiaMateriala);
                    parameters.Add("@slujireInBiserica", clientObject.SlujireInBiserica);
                    parameters.Add("@pastorLider", clientObject.PastorLider);
                    parameters.Add("@telefonPastor", clientObject.TelefonPastor);
                    parameters.Add("@personalfolder", clientObject.ClientPersonalFolder);
                    parameters.Add("@bank_id", clientObject.Bank_id);
                    parameters.Add("@contbancar", clientObject.ContBancar);
                    parameters.Add("@sortGroup", clientObject.SortGroup);

                    result = mDataBridge.ExecuteNonQuery(nonQuery, parameters); // PG compliant
                    mLastError = mDataBridge.LastError;

                    //if (clientObject.ClientPersonalFolder.Equals(string.Empty))
                    //{
                    //    string clientPersonalFolder = clientObject.ClientID.ToString();                        
                    //    UpdateClientPersonalFoldersInDB(clientObject.ClientID, clientPersonalFolder);
                    //}                    
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool AddNewClient(string firstName, string lastName, DateTime dateOfBirth, string telefonFix, string telefonMobil, string email, int sortGoup, out int clientID)
        {
            DateTime EmptyDate = DateTime.MinValue;
            clientID = 0;

            bool result = false;
            try
            {
                string nonQuery = "INSERT INTO Client (firstName, lastName, dateOfBirth,                                                         telefonFix,           telefonMobil,           email, personalID, gender, sortGroup) "
                    + " OUTPUT INSERTED.ClientID "
                    + " Values ('" + firstName + "', '" + lastName + "', " + (dateOfBirth.Equals(EmptyDate) ? "NULL" : " @dateBirth") + " , '" + telefonFix + "', '" + telefonMobil + "', '" + email + "', '' , 0, " + sortGoup + " ) ";

                Hashtable parameters = new Hashtable();
                if (!dateOfBirth.Equals(EmptyDate)) parameters.Add("@dateBirth", dateOfBirth.Date);

                object insertedID = mDataBridge.ExecuteScalarQuery(nonQuery, parameters); // PG compliant
                mLastError = mDataBridge.LastError;

                if (insertedID != null && !insertedID.ToString().Equals(string.Empty))
                {
                    clientID = (int)insertedID;
                    result = true;

                    string clientPersonalFolder = clientID.ToString();
                    UpdateClientPersonalFoldersInDB(clientID, clientPersonalFolder);
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateClientPersonalFoldersInDB(int clientID, string personalFolder)
        {
            bool result = false;
            try
            {
                string nonQuery = @"UPDATE Client SET personalfolder = '" + personalFolder + "' WHERE ClientID = " + clientID;

                result = mDataBridge.ExecuteNonQuery(nonQuery); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetClientlist(int clinetCategory)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = "Select * "
                            + " , coalesce(firstName, '') " + Plus + " coalesce(lastName, '') as \"Client Full Name\" "
                            + " , (select Name From Classifiers Where Code = gender) as gender_string "
                            + " , (select Name From Classifiers Where Code = civilStatus) as civilStatus_string "
                            + " , (select Name From Classifiers Where Code = church) as church_string "
                            + " , (select Name From Classifiers Where Code = socialStatus) as socialStatus_string "
                            + " , (select Name From Classifiers Where Code = studyLevel) as studyLevel_string "
                            + " , coalesce(telefonMobil, '') " + Plus + " coalesce(telefonFix, '') as telefon "
                            + " , (SELECT coalesce(CH.firstName, '') " + Plus + " coalesce(CH.lastName, '') FROM CLIENT as CH WHERE CH.clientID = CLIENT.HusbandID) as HusbandFullName "
                            + " , (select Name From Classifiers Where Code = SortGroup) as SortGroup_string "

                            + " FROM CLIENT"
                            + (clinetCategory != 0 ? " WHERE sortGroup = " + clinetCategory : string.Empty)
                            + " Order BY FirstName, LastName ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetClientlist()
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = "Select * "
                            + " , coalesce(firstName, '') " + Plus + " coalesce(lastName, '') as \"Client Full Name\" "
                            + " , (select Name From Classifiers Where Code = gender) as gender_string "
                            + " , (select Name From Classifiers Where Code = civilStatus) as civilStatus_string "
                            + " , (select Name From Classifiers Where Code = church) as church_string "
                            + " , (select Name From Classifiers Where Code = socialStatus) as socialStatus_string "
                            + " , (select Name From Classifiers Where Code = studyLevel) as studyLevel_string "
                            + " , (select Name From Classifiers Where Code = viza_country) as viza_country_string "
                            + " , (select Name From Classifiers Where Code = viza_raion) as viza_raion_string "
                            + " , (select Name From Classifiers Where Code = raddress_country) as raddress_country_string "
                            + " , (select Name From Classifiers Where Code = raddress_raion) as raddress_raion_string "
                            + " , (select Name From Classifiers Where Code = situatiaMateriala) as situatiaMateriala_string "
                            + " , (select Name From Bank Where BankID = bank_id) as BankName "
                            + " , (select IBAN From Bank Where BankID = bank_id) as Bank_IBAN "
                            + " , ( CASE WHEN (SELECT COUNT(*) FROM Loans WHERE Loans.clientID = client.ClientID) <> 0 THEN @NotAllowDelete ELSE @AllowDelete END ) as AllowDelete "
                            + " , (SELECT coalesce(CH.firstName, '') " + Plus + " coalesce(CH.lastName, '') FROM CLIENT as CH WHERE CH.clientID = CLIENT.HusbandID) as HusbandFullName "
                            + " , (select Name From Classifiers Where Code = SortGroup) as SortGroup_string "

                            + " FROM CLIENT"
                            + " Order BY FirstName, LastName ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@AllowDelete", true);
                parameters.Add("@NotAllowDelete", false);

                result = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetClientByID(int clientID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = "Select * "
                            + " , (select Name From Classifiers Where Code = gender) as gender_string "
                            + " , (select Name From Classifiers Where Code = civilStatus) as civilStatus_string "
                            + " , (select Name From Classifiers Where Code = church) as church_string "
                            + " , (select Name From Classifiers Where Code = socialStatus) as socialStatus_string "
                            + " , (select Name From Classifiers Where Code = studyLevel) as studyLevel_string "
                            + " , (select Name From Classifiers Where Code = viza_country) as viza_country_string "
                            + " , (select Name From Classifiers Where Code = viza_raion) as viza_raion_string "
                            + " , (select Name From Classifiers Where Code = raddress_country) as raddress_country_string "
                            + " , (select Name From Classifiers Where Code = raddress_raion) as raddress_raion_string "
                            + " , (select Name From Bank Where BankID = bank_id) as BankName "
                            + " , (select IBAN From Bank Where BankID = bank_id) as Bank_IBAN "
                            + " , ( CASE WHEN (SELECT COUNT(*) FROM Loans WHERE Loans.clientID = client.ClientID) <> 0 THEN @NotAllowDelete ELSE @AllowDelete END ) as AllowDelete "
                            + " , (SELECT coalesce(CH.firstName, '') " + Plus + " coalesce(CH.lastName, '') FROM CLIENT as CH WHERE CH.clientID = CLIENT.HusbandID) as HusbandFullName "
                            + " , (select Name From Classifiers Where Code = SortGroup) as SortGroup_string "

                            + " FROM CLIENT"
                            + " Where clientID = " + clientID;

                Hashtable parameters = new Hashtable();
                parameters.Add("@AllowDelete", true);
                parameters.Add("@NotAllowDelete", false);

                result = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public string GetClientRealAdress(int clientID)
        {
            string result = string.Empty;
            mLastError = string.Empty;

            try
            {
                string query = "Select * "
                            + " , (select Name From Classifiers Where Code = viza_country) as viza_country_string "
                            + " , (select Name From Classifiers Where Code = viza_raion) as viza_raion_string "
                            + " , (select Name From Classifiers Where Code = raddress_country) as raddress_country_string "
                            + " , (select Name From Classifiers Where Code = raddress_raion) as raddress_raion_string "
                            + " FROM CLIENT"
                            + " Where clientID = " + clientID;

                DataTable dtresult = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (dtresult != null && dtresult.Rows.Count == 1)
                {
                    if (dtresult.Rows[0]["raddress_raion_string"] != System.DBNull.Value)
                        result += "rn. " + (string)dtresult.Rows[0]["raddress_raion_string"];

                    if (dtresult.Rows[0]["raddress_localitatea"] != System.DBNull.Value)
                    {
                        if (!string.IsNullOrEmpty(result)) result += ", ";
                        result += (string)dtresult.Rows[0]["raddress_localitatea"];
                    }

                    if (dtresult.Rows[0]["raddress_stradaAdresa"] != System.DBNull.Value)
                    {
                        if (!string.IsNullOrEmpty(result)) result += ", ";
                        result += (string)dtresult.Rows[0]["raddress_stradaAdresa"];
                    }
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public Credits.DataObjects.Client GetCleintObjectByID(int clientID)
        {
            Credits.DataObjects.Client clientObject = null;

            try
            {
                string query = "WITH MinLoan as ( \r\n  "
                        + " Select " + mDataBridge.LimitStringMSSQL + " loanStartDate \r\n  "
                        + " , clientID  \r\n  "
                        + " , creditAmountValue  \r\n  "
                        + " , loanManager  \r\n  "
                        + " FROM Loans  \r\n  "
                        + " WHERE  clientID = " + clientID + " \r\n  "
                        + "  And version = (Select MAX(LN.version) From Loans as LN WHERE LN.loanID = Loans.loanID AND LN.clientID = " + clientID + ") \r\n  "
                        + " ORDER by loanStartDate ASC " + mDataBridge.LimitStringPGSQL + " \r\n  "
                        + " ) \r\n  "
                        + " , MaxLoan as ( \r\n  "
                         + " Select " + mDataBridge.LimitStringMSSQL + " loanStartDate \r\n  "
                        + " , clientID \r\n "
                        + " , creditAmountValue \r\n  "
                        + " , loanManager  \r\n   "
                        + " From Loans  \r\n  "
                        + " WHERE clientID = " + clientID + " \r\n  "
                        + "  And version = (Select MAX(LN.version) From Loans as LN WHERE LN.loanID = Loans.loanID AND LN.clientID = " + clientID + ") \r\n  "
                        + " ORDER by loanStartDate DESC " + mDataBridge.LimitStringPGSQL + " \r\n  "
                        + " ) \r\n \r\n  "

                        + "  Select \r\n "
                        + "  MinLoan.loanStartDate as ActivationDate \r\n "
                        + " , MaxLoan.creditAmountValue as LastLoanAmount \r\n "
                        + " , (SELECT nume " + mDataBridge.ConcatSimbol + " '  ' " + mDataBridge.ConcatSimbol + " Prenume FROM Users WHERE userID = MaxLoan.loanManager) as LastManager "
                        + " , (SELECT COUNT(*) FROM Loans WHERE Loans.clientID = " + clientID + "  And version = (Select MAX(LN.version) From Loans as LN WHERE LN.loanID = Loans.loanID AND LN.clientID = " + clientID + ")) as TotalLoansNumber \r\n  "
                        + " , (SELECT COUNT(*) FROM Loans WHERE Loans.clientID = " + clientID + "  And version = (Select MAX(LN.version) From Loans as LN WHERE LN.loanID = Loans.loanID AND LN.clientID = " + clientID + ") AND loanState = " + (int)Constants.Constants.Classifiers.CreditState_Activ + " ) as ActiveLoans \r\n  "
                        + " , (SELECT SUM(profit) FROM LoanOrdersPays WHERE LoanID in (Select LoanID FROM Loans WHERE Loans.clientID = " + clientID + ")) as TotalProfits \r\n  "
                        + " , (SELECT SUM(profit) FROM LoanOrdersPays WHERE LoanID in (Select LoanID FROM Loans WHERE Loans.clientID = " + clientID + " AND loanState = " + (int)Constants.Constants.Classifiers.CreditState_Activ + ")) as ActiveProfits \r\n  "

                        + " , * \r\n "
                        + " , (select Name From Classifiers Where Code = gender) as gender_string  \r\n "
                        + " , (select Name From Classifiers Where Code = civilStatus) as civilStatus_string  \r\n "
                        + " , (select Name From Classifiers Where Code = church) as church_string  \r\n "
                        + " , (select Name From Classifiers Where Code = socialStatus) as socialStatus_string  \r\n "
                        + " , (select Name From Classifiers Where Code = studyLevel) as studyLevel_string \r\n  "
                        + " , (select Name From Classifiers Where Code = viza_country) as viza_country_string \r\n  "
                        + " , (select Name From Classifiers Where Code = viza_raion) as viza_raion_string \r\n  "
                        + " , (select Name From Classifiers Where Code = raddress_country) as raddress_country_string \r\n  "
                        + " , (select Name From Classifiers Where Code = raddress_raion) as raddress_raion_string \r\n  "
                        + " , (select Name From Classifiers Where Code = situatiaMateriala) as situatiaMateriala_string \r\n  "
                        + " , (select Name From Bank Where BankID = bank_id) as BankName \r\n  "
                        + " , (select IBAN From Bank Where BankID = bank_id) as Bank_IBAN \r\n  "
                        + " , ( CASE WHEN (SELECT COUNT(*) FROM Loans WHERE Loans.clientID = " + clientID + " And version = (Select MAX(LN.version) From Loans as LN WHERE LN.loanID = Loans.loanID AND LN.clientID = " + clientID + ")) <> 0 THEN @NotAllowDelete ELSE @AllowDelete END ) as AllowDelete \r\n  "
                        + " , (SELECT coalesce(CH.firstName, '') " + Plus + " coalesce(CH.lastName, '') FROM CLIENT as CH WHERE CH.clientID = CLIENT.HusbandID) as HusbandFullName  \r\n "
                        + " , (select Name From Classifiers Where Code = SortGroup) as SortGroup_string  \r\n "
                
                        + " FROM Client \r\n  "
                        + " Left JOIN MinLoan ON MinLoan.ClientID = Client.clientID \r\n  "
                        + " Left JOIN MaxLoan ON MaxLoan.ClientID = Client.clientID \r\n  "

                        + " WHERE  \r\n  "
                        + " Client.clientID = " + clientID + " \r\n ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@AllowDelete", true);
                parameters.Add("@NotAllowDelete", false);

                DataTable result = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;

                if (result != null && result.Rows.Count == 1)
                {
                    clientObject = new Credits.DataObjects.Client(result.Rows[0]);
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return clientObject;
        }
          
        public bool UpdateClientMainData(int clientID, string firstName, string lastName, DateTime dateOfBirth, string telefonFix, string telefonMobil, string telefonMobil2, string imageURL, string email, string rootDirectory)
        {
            DateTime EmptyDate = DateTime.MinValue;

            bool result = false;
            try
            {
                if (!imageURL.Equals(string.Empty))
                {
                    string sQuery = "SELECT imageURL From Client WHERE clientID = " + clientID;
                    DataTable tempTable = mDataBridge.ExecuteQuery(sQuery);
                    if (tempTable != null && tempTable.Rows.Count == 1)
                    {
                        string oldImageLink = tempTable.Rows[0][0].ToString();
                        if (!oldImageLink.Equals(string.Empty) && System.IO.File.Exists(rootDirectory + oldImageLink))
                        {
                            System.IO.File.Delete(rootDirectory + oldImageLink);
                        }
                    }
                }

                string nonQuery = @"Update Client Set "
                            + "  firstName = @firstName "
                            + " , lastName  = @lastName "
                            + " , dateOfBirth =   " + (dateOfBirth.Equals(EmptyDate) ? "NULL" : "@dateBirth ")
                            + " , imageURL = '" + imageURL + "' "
                            + " , telefonFix = '" + telefonFix + "' "
                            + " , telefonMobil = '" + telefonMobil + "' "
                            + " , telefonMobil2 = '" + telefonMobil2 + "' "
                            + " , personalfolder = '" + clientID + "' "
                            + " , email = @email "

                            + " WHERE clientID = " + clientID;

                Hashtable parameters = new Hashtable();
                parameters.Add("@firstName", firstName);
                parameters.Add("@lastName", lastName);
                parameters.Add("@email", email);

                if (!dateOfBirth.Equals(EmptyDate)) parameters.Add("@dateBirth", dateOfBirth.Date);

                result = mDataBridge.ExecuteNonQuery(nonQuery, parameters); // PG compliant
                mLastError = mDataBridge.LastError;

            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateClientPersonalData(int clientID, string buletinSeria, string personalID, DateTime dataEliberarii, string eliberatDe, int gender, int civilStatus, int nrOfChildren, int nrPersoaneIntretinere, int studyLevel, int socialStatus, int venitLunar, int situatiaMateriala, int ciorch, string slujireInBiserica, string pastorLider, string telefonPastor, bool updateAllData)
        {
            bool result = false;
            DateTime emptyDate = DateTime.MinValue;
            try
            {
                string nonQuery = @"Update Client Set "
                            + "  personalID = '" + personalID + "' "
                            + (updateAllData ? ", buletinSeria = '" + buletinSeria + "' " : string.Empty)
                            + (updateAllData ? (!dataEliberarii.Equals(emptyDate) ? " , dataEliberarii = @dataEliberarii " : string.Empty) : string.Empty)
                            + (updateAllData ? " , eliberatDe = '" + eliberatDe + "' " : string.Empty)
                            + (updateAllData ? " , gender = " + gender : string.Empty)
                            + (updateAllData ? " , civilStatus = " + civilStatus : string.Empty)
                            + (updateAllData ? " , numberOfChildren = " + nrOfChildren : string.Empty)
                            + (updateAllData ? " , nrPersoaneIntretinere = " + nrPersoaneIntretinere : string.Empty)
                            + (updateAllData ? " , studyLevel = " + studyLevel : string.Empty)
                            + (updateAllData ? " , socialStatus = " + socialStatus : string.Empty)
                            + (updateAllData ? " , venitFamInceputAfacere = " + venitLunar : string.Empty)
                            + (updateAllData ? " , situatiaMateriala = " + situatiaMateriala : string.Empty)
                            + (updateAllData ? " , church = " + ciorch : string.Empty)
                            + (updateAllData ? " , slujireInBiserica = '" + slujireInBiserica + "' " : string.Empty)
                            + (updateAllData ? " , pastorLider = '" + pastorLider + "' " : string.Empty)
                            + (updateAllData ? " , telefonPastor = '" + telefonPastor + "' " : string.Empty)

                            + " WHERE clientID = " + clientID;

                Hashtable parameters = new Hashtable();
                if (!dataEliberarii.Equals(emptyDate)) parameters.Add("@dataEliberarii", dataEliberarii.Date);

                result = mDataBridge.ExecuteNonQuery(nonQuery, parameters); // PG compliant
                mLastError = mDataBridge.LastError;

            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateClientAddressData(int clientID, int viza_countryID, int viza_raionID, string viza_urbanRural, string viza_localitatea, string viza_adresaStrada, int real_countryID, int real_raionID, string real_urbanRural, string real_localitatea, string real_adresaStrada, bool updateRealAddress)
        {
            bool result = false;
            try
            {
                string nonQuery = @"Update Client Set "
                            + "   viza_raion = " + viza_raionID
                            + ",  viza_localitatea = '" + viza_localitatea + "' "
                            + (updateRealAddress ? ",  viza_stradaAdresa = '" + viza_adresaStrada + "' " : string.Empty)
                            + (updateRealAddress ? ",  viza_urbanRural = '" + viza_urbanRural + "' " : string.Empty)
                            + (updateRealAddress ? ",  viza_country = " + viza_countryID : string.Empty)
                            + (updateRealAddress ? ",  raddress_country = " + real_countryID : string.Empty)
                            + (updateRealAddress ? ",  raddress_raion = " + real_raionID : string.Empty)
                            + (updateRealAddress ? ",  raddress_urbanRural = '" + real_urbanRural + "' " : string.Empty)
                            + (updateRealAddress ? ",  raddress_localitatea = '" + real_localitatea + "' " : string.Empty)
                            + (updateRealAddress ? ",  raddress_stradaAdresa = '" + real_adresaStrada + "' " : string.Empty)

                            + " WHERE clientID = " + clientID;

                result = mDataBridge.ExecuteNonQuery(nonQuery); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool DeleteClient(int clientID)
        {
            bool result = false;
            try
            {
                string nonQuery = @"Delete FROM Client "
                            + " WHERE clientID = " + clientID;

                result = mDataBridge.ExecuteNonQuery(nonQuery); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool CheckExistClient(string firstName, string lastName, DateTime dateOfBirth)
        {
            DateTime EmptyDate = DateTime.MinValue;

            bool result = false;
            try
            {
                string nonQuery = @"Select * FROM Client WHERE "
                                + "   firstName like '%" + firstName + "%' "
                                + " AND lastName like '%" + lastName + "%' "
                                + (!dateOfBirth.Equals(EmptyDate) ? " AND dateOfBirth = @dateBirth " : string.Empty);

                Hashtable parameters = new Hashtable();
                if (!dateOfBirth.Equals(EmptyDate)) parameters.Add("@dateBirth", dateOfBirth.Date);

                DataTable resultTable = mDataBridge.ExecuteQuery(nonQuery, parameters); // PG compliant
                mLastError = mDataBridge.LastError;

                if (resultTable != null && resultTable.Rows.Count == 1)
                {
                    result = true;
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public int CheckExistClient(string idnp)
        {
            DateTime EmptyDate = DateTime.MinValue;

            int result = -1;

            try
            {
                string nonQuery = @"Select * FROM Client WHERE personalID = @idnp ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@idnp", idnp);

                DataTable resultTable = mDataBridge.ExecuteQuery(nonQuery, parameters); // PG compliant
                mLastError = mDataBridge.LastError;

                if (resultTable != null && resultTable.Rows.Count == 1)
                {
                    result = resultTable.Rows[0]["ClientID"] != System.DBNull.Value ? (int)resultTable.Rows[0]["ClientID"] : -1;
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public Credits.DataObjects.Client CheckClientPersonalFolders(Credits.DataObjects.Client clientObject, string rootModuleFolder)
        {
            Credits.DataObjects.Client resultClientObject = clientObject;
            try
            {
                string clientPersonalFolder = clientObject.ClientPersonalFolder;

                if (clientObject.ClientPersonalFolder.Equals(string.Empty))
                {
                    clientPersonalFolder = clientObject.ClientID.ToString();
                    clientObject.ClientPersonalFolder = clientPersonalFolder;
                }

                if (!Directory.Exists(rootModuleFolder)) { Directory.CreateDirectory(rootModuleFolder); }
                if (Directory.Exists(rootModuleFolder))
                {
                    string clientPersFolderOnDisk = rootModuleFolder + "/" + clientPersonalFolder;

                    if (!Directory.Exists(clientPersFolderOnDisk)) { Directory.CreateDirectory(clientPersFolderOnDisk); }
                    if (Directory.Exists(clientPersFolderOnDisk))
                    {
                        string consultEvalPhotoFolder = clientPersFolderOnDisk + "/" + ConsultEvalPhotoFolderName;
                        string consultEvalPhotoFolderLarge = clientPersFolderOnDisk + "/" + ConsultEvalPhotoFolderLargeName;
                        string consultEvalPhotoFolderTumbs = clientPersFolderOnDisk + "/" + ConsultEvalPhotoFolderTumbsName;

                        string contractsFolder = clientPersFolderOnDisk + "/" + ContractsFolderName;
                        string personalDocuments = clientPersFolderOnDisk + "/" + PersonalDocumentsFolderName;

                        if (!Directory.Exists(consultEvalPhotoFolder)) { Directory.CreateDirectory(consultEvalPhotoFolder); }
                        if (!Directory.Exists(consultEvalPhotoFolderLarge)) { Directory.CreateDirectory(consultEvalPhotoFolderLarge); }
                        if (!Directory.Exists(consultEvalPhotoFolderTumbs)) { Directory.CreateDirectory(consultEvalPhotoFolderTumbs); }
                        if (!Directory.Exists(contractsFolder)) { Directory.CreateDirectory(contractsFolder); }
                        if (!Directory.Exists(personalDocuments)) { Directory.CreateDirectory(personalDocuments); }

                        if (Crypt.Utils.IsDirectoryEmpty(personalDocuments))
                        {
                            ///// Additional folders
                            string folder_analiza = personalDocuments + "/analiza";
                            string folder_contrScan = personalDocuments + "/contr.scan";
                            string folder_docScan = personalDocuments + "/doc.scan";
                            string folder_Poze = personalDocuments + "/poze";
                            if (!Directory.Exists(folder_analiza)) { Directory.CreateDirectory(folder_analiza); }
                            if (!Directory.Exists(folder_contrScan)) { Directory.CreateDirectory(folder_contrScan); }
                            if (!Directory.Exists(folder_docScan)) { Directory.CreateDirectory(folder_docScan); }
                            if (!Directory.Exists(folder_Poze)) { Directory.CreateDirectory(folder_Poze); }
                        }
                    }
                }

                //UpdateClientPersonalFoldersInDB(clientObject.ClientID, clientPersonalFolder);
                resultClientObject = clientObject;
            }
            catch (Exception ex)
            {
                mLastError = ex.Message;
            }

            return resultClientObject;
        }

        public bool TransformImageFile(System.IO.Stream inputFileStream, string fileName, Credits.DataObjects.Client clientObject, out string finalLargeFullFileName, out string finalSmallFullFileName, out string originalFileName)
        {
            bool result = false;

            finalLargeFullFileName = string.Empty;
            finalSmallFullFileName = string.Empty;
            originalFileName = string.Empty;

            try
            {
                Dictionary<string, string> versions = new Dictionary<string, string>();
                //Define the version to generate
                versions.Add("_thumb", "width=150&height=150&format=jpg&mode=max");
                versions.Add("_medium", "maxwidth=1000&maxheight=1000format=jpg");

                originalFileName = fileName;

                string creditModuleFileDirectory = this.RootModuleFolder;
                string clientPersonalFolder = clientObject.ClientPersonalFolder;

                System.IO.Stream file = new System.IO.MemoryStream(Crypt.Utils.ToByteArray(inputFileStream));
                System.IO.Stream file2 = new System.IO.MemoryStream(Crypt.Utils.ToByteArray(inputFileStream));


                ImageJob im = new ImageJob(file, creditModuleFileDirectory + "/" + clientPersonalFolder + "/" + ConsultEvalPhotoFolderLargeName + "/<guid>.<ext>", new ResizeSettings(versions["_medium"]));
                im.CreateParentDirectory = true; //Auto-create the uploads directory.
                im.Build();

                finalLargeFullFileName = string.Empty;
                string[] fullPathB = im.FinalPath.Split('\\');

                if (fullPathB != null && fullPathB.Length > 1)
                {
                    finalLargeFullFileName = clientPersonalFolder + "/" + ConsultEvalPhotoFolderLargeName + "/" + fullPathB[fullPathB.Length - 1];
                }

                ImageJob i = new ImageJob(file2, creditModuleFileDirectory + "/" + clientPersonalFolder + "/" + ConsultEvalPhotoFolderTumbsName + "/<guid>.<ext>", new ResizeSettings(versions["_thumb"]));
                i.CreateParentDirectory = true; //Auto-create the uploads directory.
                i.Build();

                finalSmallFullFileName = string.Empty;
                string[] fullPath = i.FinalPath.Split('\\');

                if (fullPath != null && fullPath.Length > 1)
                {
                    finalSmallFullFileName = clientPersonalFolder + "/" + ConsultEvalPhotoFolderTumbsName + "/" + fullPath[fullPath.Length - 1];
                }

                result = true;
            }
            catch (Exception ex)
            {
                mLastError = ex.Message;
            }

            return result;
        }
        #endregion Client Region

        #region Client Businesss

        public DataTable GetBussinesByClientID(int clientID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = "Select "
                            + " clientID, "
                            + " sector_afacere, "
                            + " descriereActivitate, "
                            + " venit_lunar_afacere, "
                            + " mijloadeInceputAfacere, "
                            + " formaInregistrareAfacere, "
                            + " SeriaNrCertInregistrareAfacere, "
                            + " bankID, "
                            + " contBancar, "
                            + " aniExperientaInDomeniu, "
                            + " aniTotalExperientaAfaceri, "
                            + " bussinessStartDate, "
                            + " suprafataTerenAgricol, "
                            + " claridiComerciale, "
                            + " animale, "
                            + " vehicoleComerciale, "
                            + " nrAngajati, "
                            + " datorii, "
                            + " tipafacere "

                            + " FROM Business"
                            + " WHERE clientID = " + clientID;

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateClientBussines(int clientID, int sector_afacere, string descriereActivitare, int formaInregistrareAfacere, decimal aniExperientaInDomeniu, decimal aniTotalExperientaAfaceri, DateTime bussinessStartDate, string suprafataTerenAgricol, int claridiComerciale, int animale, int vehicoleComerciale, int nrAngajati, int datorii, string tipafacere)
        {
            bool result = false;
            DateTime emptyDate = DateTime.MinValue;

            try
            {
                string selectQuery = "Select * FROM Business WHERE clientID = " + clientID;
                DataTable existBussiness = mDataBridge.ExecuteQuery(selectQuery);

                if (existBussiness != null && existBussiness.Rows.Count == 1)
                {
                    string nonQuery = @"UPDATE Business SET "
                                + " sector_afacere = " + sector_afacere
                                + " , descriereActivitate = '" + descriereActivitare + "' "
                                + " , formaInregistrareAfacere = " + formaInregistrareAfacere
                                + " , aniExperientaInDomeniu = @aniExperientaInDomeniu "
                                + " , aniTotalExperientaAfaceri = @aniTotalExperientaAfaceri  "
                                + (!bussinessStartDate.Equals(emptyDate) ? " , bussinessStartDate = @bussinessStartDate  " : string.Empty)
                                + " , suprafataTerenAgricol = '" + suprafataTerenAgricol + "' "
                                + " , claridiComerciale = " + claridiComerciale
                                + " , animale = " + animale
                                + " , vehicoleComerciale = " + vehicoleComerciale
                                + " , nrAngajati = " + nrAngajati
                                + " , datorii = " + datorii
                                + " , tipafacere = '" + tipafacere + "' "

                                + " WHERE clientID = " + clientID;

                    Hashtable parameters = new Hashtable();
                    parameters.Add("@aniExperientaInDomeniu", aniExperientaInDomeniu);
                    parameters.Add("@aniTotalExperientaAfaceri", aniTotalExperientaAfaceri);
                    if (!bussinessStartDate.Equals(emptyDate)) parameters.Add("@bussinessStartDate", bussinessStartDate);

                    result = mDataBridge.ExecuteNonQuery(nonQuery, parameters); // PG compliant
                    mLastError = mDataBridge.LastError;
                }
                else
                {
                    string nonQuery = @"INSERT INTO Business (  clientID,  sector_afacere,           descriereActivitate,          formaInregistrareAfacere,     aniExperientaInDomeniu,   aniTotalExperientaAfaceri,    bussinessStartDate,     suprafataTerenAgricol,         claridiComerciale,          animale,        vehicoleComerciale,         nrAngajati,         datorii , tipafacere) "
                                            + " Values (" + clientID + ",          " + sector_afacere + " , '" + descriereActivitare + "', " + formaInregistrareAfacere + ", @aniExperientaInDomeniu , @aniTotalExperientaAfaceri , " + (!bussinessStartDate.Equals(emptyDate) ? "@bussinessStartDate" : "NULL") + ", '" + suprafataTerenAgricol + "', " + claridiComerciale + ", " + animale + ", " + vehicoleComerciale + ", " + nrAngajati + ", " + datorii + ", '" + tipafacere + "' ) ";

                    Hashtable parameters = new Hashtable();
                    parameters.Add("@aniExperientaInDomeniu", aniExperientaInDomeniu);
                    parameters.Add("@aniTotalExperientaAfaceri", aniTotalExperientaAfaceri);
                    if (!bussinessStartDate.Equals(emptyDate)) parameters.Add("@bussinessStartDate", bussinessStartDate);

                    result = mDataBridge.ExecuteNonQuery(nonQuery, parameters); // PG compliant
                    mLastError = mDataBridge.LastError;
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }
        #endregion Client Business

        #region Funding lines

        public DataTable GetFundingLinesById(string LinesID)
        {
            DataTable resultDataTable = new DataTable();

            try
            {
                string commandText = @"SELECT * "
                      + " , (select Name From Currency Where CurrencyID = FundingLines.CurrencyID) as CurrencyName "
                      + " FROM FundingLines "
                      + " WHERE LinesID = " + LinesID;

                resultDataTable = mDataBridge.ExecuteQuery(commandText); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return resultDataTable;
        }

        public DataTable GetFundingLinesList()
        {
            DataTable resultDataTable = new DataTable();

            try
            {
                string commandText = @"SELECT * "
                                     + " , (select Name From Currency Where CurrencyID = FundingLines.CurrencyID) as Currency "
                                     + " FROM FundingLines "
                                     + " ORDER BY Name ";

                resultDataTable = mDataBridge.ExecuteQuery(commandText); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return resultDataTable;
        }

        public bool AddFundingLines(string name, string code, int currencyID, DateTime dateBegin, DateTime dateEnd, decimal initialAmount, decimal realRemainder, decimal anticipatedRemainder)
        {
            bool result = false;
            DateTime emptyDate = DateTime.MinValue;

            try
            {
                string nonQuery = @"INSERT INTO FundingLines (name, code, currencyID, dateBegin, dateEnd, initialAmount, realRemainder, anticipatedRemainder)"
                    + " VALUES ( '" + name + "', '" + code + "', " + currencyID + ", " + (!emptyDate.Equals(dateBegin) ? "@dateBegin" : "NULL") + ", " + (!emptyDate.Equals(dateEnd) ? "@dateEnd" : "NULL") + " ,@initialAmount,@realRemainder,@anticipatedRemainder)";

                Hashtable parameters = new Hashtable();
                if (!emptyDate.Equals(dateBegin)) parameters.Add("@dateBegin", dateBegin.Date);
                if (!emptyDate.Equals(dateEnd)) parameters.Add("@dateEnd", dateEnd.Date);
                parameters.Add("@initialAmount", initialAmount);
                parameters.Add("@realRemainder", realRemainder);
                parameters.Add("@anticipatedRemainder", anticipatedRemainder);

                result = mDataBridge.ExecuteNonQuery(nonQuery, parameters); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateFundingLines(int linesID, string name, string code, int currencyID, DateTime dateBegin, DateTime dateEnd, decimal initialAmount, decimal realRemainder, decimal anticipatedRemainder)
        {
            bool result = false;
            DateTime emptyDate = DateTime.MinValue;

            try
            {
                string nonQuery = @"Update FundingLines "
                    + " SET Name = '" + name + "' "
                    + ", Code = '" + code + "' "
                    + ", CurrencyID = " + currencyID
                    + ", DateBegin = " + (!emptyDate.Equals(dateBegin) ? "@dateBegin" : "NULL")
                    + ", DateEnd = " + (!emptyDate.Equals(dateEnd) ? "@dateEnd" : "NULL")
                    + ", InitialAmount = @InitialAmount "
                    + ", RealRemainder = @RealRemainder "
                    + ", AnticipatedRemainder = @AnticipatedRemainder "
                    + " WHERE LinesID = " + linesID;

                Hashtable parameters = new Hashtable();
                if (!emptyDate.Equals(dateBegin)) parameters.Add("@dateBegin", dateBegin.Date);
                if (!emptyDate.Equals(dateEnd)) parameters.Add("@dateEnd", dateEnd.Date);
                parameters.Add("@InitialAmount", initialAmount);
                parameters.Add("@RealRemainder", realRemainder);
                parameters.Add("@AnticipatedRemainder", anticipatedRemainder);

                result = mDataBridge.ExecuteNonQuery(nonQuery, parameters); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool DeleteFundingLines(int linesID)
        {
            bool result = false;
            try
            {
                string nonQuery = @"DELETE FROM FundingLines WHERE LinesID = " + linesID;

                result = mDataBridge.ExecuteNonQuery(nonQuery); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        #endregion Funding lines

        #region Trainings

        public DataTable GetTrainingList(int trainingID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT "
                            + " trainingID "
                            + " , trainingDate "
                            + " , tipulInstruirii_ID "
                            + " , (Select Name From Classifiers Where Code = tipulInstruirii_ID) as tipulInstruirii_ID_string  "
                            + " , DescrirereaInstruirii "
                            + "  , cast(trainingDate as varchar)  " + mDataBridge.ConcatSimbol + "  ' - '  " + mDataBridge.ConcatSimbol + "  DescrirereaInstruirii as \"Trainig Full Description\"  "
                            + " , trainingName as TrainingName "
                            + " , trainingScop "
                            + " , trainingPhoto "

                            + " FROM Trainings "
                            + (trainingID != 0 ? " WHERE trainingID = " + trainingID : string.Empty)
                            + " ORDER BY trainingDate DESC ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateTraining(int trainingID, DateTime trainingDate, List<int> trainersID, int tipulInstruirii_ID, List<int> ClientiImplicatiID, string DescrirereaInstruirii, string trainingName, string trainingScop, string trainigPhotoLink)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " UPDATE Trainings SET "
                           + " trainingDate = @trainingDate "
                           + " , tipulInstruirii_ID = " + tipulInstruirii_ID
                           + " , DescrirereaInstruirii = '" + DescrirereaInstruirii + "'  "
                           + " , trainingName = '" + trainingName + "'  "
                           + " , trainingScop = '" + trainingScop + "'  "
                           + " , trainingPhoto = '" + trainigPhotoLink + "'  "

                           + " Where trainingID = " + trainingID;

                Hashtable parameters = new Hashtable();
                parameters.Add("@trainingDate", trainingDate.Date);

                bool tempResult = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;

                if (tempResult)
                {
                    string deleteQuery = "Delete From TrainingOperators WHERE TrainingID = " + trainingID;
                    bool deleteResut = mDataBridge.ExecuteNonQuery(deleteQuery);
                    mLastError = mDataBridge.LastError;

                    deleteQuery = "Delete From TrainingClients WHERE TrainingID = " + trainingID;
                    deleteResut = mDataBridge.ExecuteNonQuery(deleteQuery);
                    mLastError = mDataBridge.LastError;

                    int countSeccesifullTrainerInsert = 0;
                    for (int i = 0; i < trainersID.Count; i++)
                    {
                        string insertQuery = "INSERT INTO TrainingOperators (TrainingID, OperatorID) "
                                        + " Values (" + trainingID + ", " + trainersID[i] + ") ";

                        bool tempResut = mDataBridge.ExecuteNonQuery(insertQuery);
                        mLastError = mDataBridge.LastError;

                        if (tempResut) countSeccesifullTrainerInsert++;
                    }

                    int countSeccesifullClientInsert = 0;
                    for (int j = 0; j < ClientiImplicatiID.Count; j++)
                    {
                        string insertQuery = "INSERT INTO TrainingClients (TrainingID, clientID) "
                                        + " Values (" + trainingID + ", " + ClientiImplicatiID[j] + " ) ";

                        bool tempReult = mDataBridge.ExecuteNonQuery(insertQuery);
                        mLastError = mDataBridge.LastError;

                        if (tempReult) countSeccesifullClientInsert++;
                    }

                    if ((countSeccesifullTrainerInsert == trainersID.Count) && (countSeccesifullClientInsert == ClientiImplicatiID.Count))
                    {
                        result = true;
                    }
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool DeleteTraining(int trainingID)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string deleteQuery = "Delete From TrainingOperators WHERE TrainingID = " + trainingID;
                bool deleteResut = mDataBridge.ExecuteNonQuery(deleteQuery);
                mLastError = mDataBridge.LastError;

                deleteQuery = "Delete From TrainingClients WHERE TrainingID = " + trainingID;
                deleteResut = mDataBridge.ExecuteNonQuery(deleteQuery);
                mLastError = mDataBridge.LastError;

                deleteQuery = "Delete From Trainings WHERE trainingID = " + trainingID;
                result = mDataBridge.ExecuteNonQuery(deleteQuery);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool AddNewTraining(DateTime trainingDate, List<int> trainersID, int tipulInstruirii_ID, List<int> ClientiImplicatiID, string DescrirereaInstruirii, string trainingName, string trainingScop, string trainigPhotoLink)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " INSERT INTO Trainings (trainingDate, tipulInstruirii_ID, DescrirereaInstruirii, trainingName, trainingScop, trainingPhoto) "
                            + " OUTPUT INSERTED.trainingID "
                            + " VALUES (@trainingDate, " + tipulInstruirii_ID + ", '" + DescrirereaInstruirii + "', '" + trainingName + "', '" + trainingScop + "', '" + trainigPhotoLink + "')";

                Hashtable parameters = new Hashtable();
                parameters.Add("@trainingDate", trainingDate.Date);

                object insertedID = mDataBridge.ExecuteScalarQuery(query, parameters);
                mLastError = mDataBridge.LastError;

                if (insertedID != null)
                {
                    int insertedTrainingID = (int)insertedID;

                    int countSeccesifullTrainerInsert = 0;
                    for (int i = 0; i < trainersID.Count; i++)
                    {
                        string insertQuery = "INSERT INTO TrainingOperators (TrainingID, OperatorID) "
                                        + " Values (" + insertedTrainingID + ", " + trainersID[i] + ") ";

                        bool tempResut = mDataBridge.ExecuteNonQuery(insertQuery);
                        mLastError = mDataBridge.LastError;

                        if (tempResut) countSeccesifullTrainerInsert++;
                    }

                    int countSeccesifullClientInsert = 0;
                    for (int j = 0; j < ClientiImplicatiID.Count; j++)
                    {
                        string insertQuery = "INSERT INTO TrainingClients (TrainingID, clientID) "
                                        + " Values (" + insertedTrainingID + ", " + ClientiImplicatiID[j] + " ) ";

                        bool tempReult = mDataBridge.ExecuteNonQuery(insertQuery);
                        mLastError = mDataBridge.LastError;

                        if (tempReult) countSeccesifullClientInsert++;
                    }

                    if ((countSeccesifullTrainerInsert == trainersID.Count) && (countSeccesifullClientInsert == ClientiImplicatiID.Count))
                    {
                        result = true;
                    }
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetTrainingOperatorsListByTrainingID(int trainingID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT "
                            + " trainingID "
                            + " , OperatorID "
                            + " , (select coalesce(Nume, '') " + mDataBridge.ConcatSimbol + " ' ' " + mDataBridge.ConcatSimbol + " coalesce(Prenume, '') From Operators Where Operators.OperatorID = TrainingOperators.OperatorID ) as \"Operator Full Name\" "

                            + " FROM TrainingOperators "
                            + " WHERE trainingID = " + trainingID;

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetClientsListByTrainingID(int trainingID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT "
                            + " trainingID "
                            + " , ClientID "

                            + " FROM TrainingClients "
                            + " WHERE trainingID = " + trainingID;

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetTrainingsListByClientID(int clientID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT "
                            + " trainingID "
                            + " , trainingDate "
                            + " , trainingName "
                            + " , trainingScop "
                            + " , (SELECT Name FROM Classifiers WHERE Code = tipulInstruirii_ID ) as tipulInstruirii "
                            + " , DescrirereaInstruirii "
                            + " FROM Trainings  "
                            + " WHERE  "
                            + " trainingID in (SELECT TC.trainingID FROM TrainingClients as TC WHERE TC.clientID = " + clientID + ") "
                            + " ORDER BY trainingDate DESC ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool AddOneClientToTraining(int trainingID, int clientID)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = "INSERT INTO TrainingClients (trainingID, clientID) "
                                        + " Values (" + trainingID + ", " + clientID + ") ";

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool DeleteOneClientFromTraining(int trainingID, int clientID)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = "DELETE FROM TrainingClients WHERE trainingID = " + trainingID + " AND clientID = " + clientID + " ";

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }
        #endregion Trainings

        #region Loan Products

        public DataTable GetLoanProductsDisplayList()
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT "
                            + " productID "
                            + " , productName "
                            + " , productCode "
                    //+ " , (Select name from Currency where Currency.CurrencyID = LoanProducts.CurrencyID) as \"Currency_String\" "

                            + " , ( CASE When ((percentValue = 0) OR (percentValue is NULL )) "
                            + "   THEN ( 'Min:' " + mDataBridge.ConcatSimbol + " cast(percentMin as varchar) " + mDataBridge.ConcatSimbol + " '   Max:' " + mDataBridge.ConcatSimbol + " cast(percentMax as varchar) ) "
                            + "   ELSE cast(percentValue as varchar) "
                            + "   END ) as \"Percents_string\" "


                            + " , ( CASE When ((paymnetsCountValue = 0) OR (paymnetsCountValue is NULL )) "
                            + "   THEN ( 'Min:' " + mDataBridge.ConcatSimbol + " cast(paymentsCountMin as varchar) " + mDataBridge.ConcatSimbol + " '   Max:' " + mDataBridge.ConcatSimbol + " cast(paymnetsCountMax as varchar) ) "
                            + "   ELSE cast(paymnetsCountValue as varchar) "
                            + "   END ) as \"paymentsCount_string\" "


                            + " , ( CASE When ((preferentialPeriodValue = 0) OR (preferentialPeriodValue is NULL )) "
                            + "   THEN ( 'Min:' " + mDataBridge.ConcatSimbol + " cast(preferentialPeriodMin as varchar) " + mDataBridge.ConcatSimbol + " '   Max:' " + mDataBridge.ConcatSimbol + " cast(preferentialPeriodMax as varchar) ) "
                            + "   ELSE cast(preferentialPeriodValue as varchar)"
                            + "   END ) as \"preferentialPeriod_string\" "

                            + " FROM LoanProducts ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetLoanProductByID(int loanProductID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT * "
                    //+ " , (Select Name From FundingLines Where LinesID = fundingLineID) as \"fundingLineID_string\" "
                            + " FROM LoanProducts WHERE productID = " + loanProductID;

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool DeleteLoanProduct(int productID)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string deleteQuery = "Delete From LoanProducts WHERE productID = " + productID;
                result = mDataBridge.ExecuteNonQuery(deleteQuery);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool AddNewLoanProduct(string productName, string productCode, int gracePeriodMin, int gracePeriodMax, int gracePeriodValue, int creditAmountMin, int creditAmountMax, int creditAmountValue, decimal percentMin, decimal percentMax, decimal percentValue, int paymentsCountMin, int paymnetsCountMax, int paymnetsCountValue, int calculatePercentFrom, decimal penaltyPercentMin, decimal penaltyPercentMax, decimal penaltyPercentValue, int calculatePenaltyFrom, int currencyID, int minimPercentPeriod)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = @" INSERT INTO LoanProducts( productName,        productCode,        preferentialPeriodMin, preferentialPeriodMax,  preferentialPeriodValue,     creditAmountMin,           creditAmountMax,         creditAmountValue,  percentMin,     percentMax,  percentValue,   paymentsCountMin,           paymnetsCountMax,          paymnetsCountValue,         calculatePercentFrom,  penaltyPercentMin,   penaltyPercentMax,  penaltyPercentValue , calculatePenaltyFrom, currencyID, minimPercentPeriod) "
                                        + " VALUES ( '" + productName + "', '" + productCode + "', " + gracePeriodMin + " , " + gracePeriodMax + ", " + gracePeriodValue + ", " + creditAmountMin + ", " + creditAmountMax + ", " + creditAmountValue + ", @percentMin , @percentMax, @percentValue, " + paymentsCountMin + ", " + paymnetsCountMax + ", " + paymnetsCountValue + ", " + calculatePercentFrom + ", @penaltyPercentMin, @penaltyPercentMax, @penaltyPercentValue, " + calculatePenaltyFrom + ", " + currencyID + ", " + minimPercentPeriod + ")";

                //+ " OUTPUT INSERTED.productID "                          

                Hashtable parameters = new Hashtable();
                parameters.Add("@percentMin", percentMin);
                parameters.Add("@percentMax", percentMax);
                parameters.Add("@percentValue", percentValue);
                parameters.Add("@penaltyPercentMin", penaltyPercentMin);
                parameters.Add("@penaltyPercentMax", penaltyPercentMax);
                parameters.Add("@penaltyPercentValue", penaltyPercentValue);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateLoanProduct(string productID, string productName, string productCode, int gracePeriodMin, int gracePeriodMax, int gracePeriodValue, int creditAmountMin, int creditAmountMax, int creditAmountValue, decimal percentMin, decimal percentMax, decimal percentValue, int paymentsCountMin, int paymnetsCountMax, int paymnetsCountValue, int calculatePercentFrom, decimal penaltyPercentMin, decimal penaltyPercentMax, decimal penaltyPercentValue, int calculatePenaltyFrom, int currencyID, int minimPercentPeriod)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " Update LoanProducts SET  "
                            + " productName = '" + productName + "' "
                            + " , productCode = '" + productCode + "' "
                            + ", preferentialPeriodMin = " + gracePeriodMin
                            + ", preferentialPeriodMax = " + gracePeriodMax
                            + ", preferentialPeriodValue = " + gracePeriodValue
                            + ", creditAmountMin = " + creditAmountMin
                            + ", creditAmountMax = " + creditAmountMax
                            + ", creditAmountValue = " + creditAmountValue
                            + ", percentMin = @percentMin "
                            + ", percentMax = @percentMax "
                            + ", percentValue = @percentValue "
                            + ", paymentsCountMin = " + paymentsCountMin
                            + ", paymnetsCountMax = " + paymnetsCountMax
                            + ", paymnetsCountValue = " + paymnetsCountValue
                            + ", calculatePercentFrom = " + calculatePercentFrom
                            + ", penaltyPercentMin = @penaltyPercentMin "
                            + ", penaltyPercentMax = @penaltyPercentMax "
                            + ", penaltyPercentValue = @penaltyPercentValue "
                            + ", calculatePenaltyFrom = " + calculatePenaltyFrom
                            + ", currencyID = " + currencyID
                            + ", minimPercentPeriod = " + minimPercentPeriod

                            + " WHERE productID = " + productID;

                Hashtable parameters = new Hashtable();
                parameters.Add("@percentMin", percentMin);
                parameters.Add("@percentMax", percentMax);
                parameters.Add("@percentValue", percentValue);
                parameters.Add("@penaltyPercentMin", penaltyPercentMin);
                parameters.Add("@penaltyPercentMax", penaltyPercentMax);
                parameters.Add("@penaltyPercentValue", penaltyPercentValue);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }   
        #endregion Loan Products

        #region Client Loans

        public DataTable GetLoansList(int loanState, int clientID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " Select * "
                            + " , (Select coalesce(firstName, '') " + mDataBridge.ConcatSimbol + " ' ' " + mDataBridge.ConcatSimbol + " coalesce(lastName, '') FROM Client WHERE Client.ClientID = Loans.ClientID) as \"clientName\" "
                            + " , (SELECT Name From Classifiers Where Classifiers.Code = payPeriodID) as \"payPeriodID_string\" "
                            + " , (SELECT Name From FundingLines Where LinesID = fundingLineID) as \"fundingLineID_string\" "
                            + " , (SELECT Name From Classifiers Where Classifiers.Code = loanState) as \"loanState_string\" "

                            + " FROM loans "
                            + " WHERE version = (SELECT MAX(LS.Version) FROM Loans as LS WHERE LS.loanID = loans.loanID) "
                            + (loanState != 0 ? " AND loanState = " + loanState : string.Empty)
                            + (clientID != 0 ? " AND clientID = " + clientID : string.Empty)
                            + " ORDER BY contractCode ASC";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetLoansListDebits()
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = @"SELECT distinct loanid "
                    + ", (select DATEDIFF(day,GETDATE(),calculatedData) from LoanOrders L2 where L2.LoanID=L1.LoanID and l2.iterationID=(SELECT MIN(iterationID) from LoanOrders L3 where L3.LoanID=L1.LoanID and L3.payedOrderNumber='' )) AS DaysDelay "
                    + ", (select contractCode from Loans where Loans.loanID=L1.LoanID and Loans.version = (SELECT MAX(LS.Version) FROM Loans as LS WHERE LS.loanID = loans.loanID)) as contractCode "
                    + ", (Select coalesce(C.firstName, '')  +  ' '  +  coalesce(C.lastName, '') FROM Client C INNER JOIN Loans ON C.ClientID = Loans.ClientID WHERE Loans.loanID=l1.LoanID	and Loans.version = (SELECT MAX(LS.Version) FROM Loans as LS WHERE LS.loanID = loans.loanID)) as clentName "
                    + "  FROM [capital].[dbo].[LoanOrders] L1 where payedOrderNumber=''  order by DaysDelay";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetLoansListForFilter()
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " Select loanID "
                            + " , cast(contractCode as nvarchar) " + mDataBridge.ConcatSimbol + " ' (' " + mDataBridge.ConcatSimbol + "  convert(varchar,loanStartDate,104) + ') ' " + mDataBridge.ConcatSimbol + "   (Select coalesce(firstName, '') " + mDataBridge.ConcatSimbol + " ' ' " + mDataBridge.ConcatSimbol + " coalesce(lastName, '') FROM Client WHERE Client.ClientID = Loans.ClientID) as \"clentName\" "
                            + " FROM loans "
                            + " WHERE version = (SELECT MAX(LS.Version) FROM Loans as LS WHERE LS.loanID = loans.loanID) "
                            + " ORDER BY contractCode ASC";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public Credits.DataObjects.LoanObject GetLoanObjectByID(int loanID)
        {
            Credits.DataObjects.LoanObject result = null;
            mLastError = string.Empty;

            try
            {
                string query = " Select "
                            + "  (Select coalesce(FirstName, '') " + Plus + " coalesce(LastName, '') From Client Where ClientID = loans.ClientID) as \"Client Description\"   \r\n "
                            + " , (Select Name From FundingLines Where LinesID = loans.fundingLineID) as \"Credit Funding Line\" \r\n  "
                            + " , (Select Name From Classifiers Where Code = loans.calculatePercentFrom) as calculatePercentFromString  \r\n "
                            + " , (Select Name From Classifiers Where Code = loans.calculatePenaltyFrom) as calclatePEnaltyFromString \r\n  "
                            + " , (Select Name From Classifiers Where Code = loans.payPeriodID) as payPeriodIDString \r\n  "
                            + " , (Select coalesce(Nume, '') " + Plus + " coalesce(Prenume, '') From Users Where UserID = loans.modifiedBy) as modifiedByString  \r\n  "
                            + " , (Select Name From Classifiers Where Code = loans.loanCategory) as loanCategoryString  \r\n "
                            + " , (Select Name From Classifiers WHERE Code = (Select viza_country From Client Where ClientID = loans.ClientID)) as \"Client Country\"  \r\n  "
                            + " , (Select Name From Classifiers WHERE Code = (Select viza_raion From Client Where ClientID = loans.ClientID)) as \"Client Raion\" \r\n   "
                            + " , (Select viza_localitatea From Client Where ClientID = loans.ClientID) as \"Client Localitatea\"  \r\n  "
                            + " , (Select viza_stradaAdresa From Client Where ClientID = loans.ClientID) as \"Client StradaAdresa\"  \r\n  "
                            + " , (Select buletinSeria From Client Where ClientID = loans.ClientID) as \"Client Buletin\"  \r\n  "
                            + " , (Select eliberatDe From Client Where ClientID = loans.ClientID) as \"Client Buletin Eliberat De\"  \r\n  "
                            + " , (Select dataEliberarii From Client Where ClientID = loans.ClientID) as \"Client data eliberarii buletin\"  \r\n  "
                            + " , (Select personalID From Client Where ClientID = loans.ClientID) as \"Client PersonalID\"  \r\n  "
                            + " , (Select DateOfBirth From Client Where ClientID = loans.ClientID) as \"Client BirthDate\"   \r\n "
                            + " , (Select telefonFix From Client Where ClientID = loans.ClientID) as \"Client Telefon Fix\"  \r\n  "
                            + " , (Select telefonMobil From Client Where ClientID = loans.ClientID) as \"Client Telefon Mobil\"  \r\n  "
                            + " , (Select telefonMobil2 From Client Where ClientID = loans.ClientID) as \"Client Telefon Mobil2\"  \r\n  "
                            + " , (Select contbancar From Client Where ClientID = loans.ClientID) as \"Client Contbancar\"  \r\n  "
                            + " , (Select Name From Bank WHERE Bank.BankID = (Select Client.bank_id From Client WHERE Client.ClientID = loans.ClientID)) as \"Client Bank Name\"  \r\n "
                            + " , (Select IBAN From Bank WHERE Bank.BankID = (Select Client.bank_id From Client WHERE Client.ClientID = loans.ClientID)) as \"Client Bank IBAN\" \r\n  "
                            + " ,  creditAmountValue * currencyChangeRate as \"Contract Amount in Lei\"  \r\n "
                            + " , (SELECT Name From Classifiers Where Classifiers.Code = loanState) as \"loanState_string\"  \r\n "
                            + " , (Select coalesce(Nume, '') " + Plus + " coalesce(Prenume, '') From Users Where userid = loans.LoanManager) as \"LoanManagerIDString\" \r\n   "
                            + "  , case when loans.currencyChangeRate IS null OR loans.currencyChangeRate = 0  \r\n   "
                            + "         THEN RateHistroy.rate \r\n   "
                            + "         ELSE \r\n   "
                            + "         loans.currencyChangeRate \r\n   "
                            + "         END as currencyChangeRate \r\n   "
                            + " , *  \r\n   "

                            + " FROM loans \r\n  "
                            + "  LEFT JOIN RateHistroy on loans.currencyID = RateHistroy.currency \r\n  "
                            + "  AND loans.contractDate = RateHistroy.data  \r\n  "
                            + " Where loanID = " + loanID + " \r\n "
                            + " AND version = (SELECT MAX(Version) FROM Loans WHERE loanID = " + loanID + ")  \r\n ";

                DataTable tempResult = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (tempResult != null && tempResult.Rows.Count == 1)
                {
                    result = new DataObjects.LoanObject(tempResult.Rows[0]);
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateLoan(Credits.DataObjects.LoanObject loanObject)
        {
            bool result = false;
            DateTime emptyDate = DateTime.MinValue;

            try
            {
                string query = "SELECT MAX(version) FROM Loans WHERE loanID = " + loanObject.LoanID;
                object maxID = mDataBridge.ExecuteScalarQuery(query);
                mLastError = mDataBridge.LastError;

                if (maxID != null && !maxID.ToString().Equals(string.Empty))
                {
                    loanObject.LoanVersion = (int)maxID;
                    loanObject.LoanVersion++;
                }

                if (maxID != null && loanObject.LoanVersion != 0)
                {
                    loanObject.VersionDate = DateTime.Now;

                    query = " INSERT INTO Loans (loanID,  version,  versionDate,  productID,  clientID, loanStartDate,   loanEndDate,    payPeriodID,  fundingLineID,  creditAmountValue,  nrOfCreditMonthValue, percentValue,   calculatePercentFrom,  penaltyValue,  calculatePenaltyFrom,  loanScope,  modifiedBy,  contractCode,  loanCategory, penaltyGracePeriod,    programID,  currencyID,  currencyChangeRate,  loanState,                        requestDate,                                                                              contractDate,                                  loanManager) "
                                    + " VALUES (@loanID, @version, @versionDate, @productID, @clientID, @loanStartDate,  @loanEndDate , @payPeriodID, @fundingLineID, @creditAmountValue, @nrOfCreditMonthValue, @percentValue, @calculatePercentFrom, @penaltyValue, @calculatePenaltyFrom, @loanScope, @modifiedBy, @contractCode, @loanCategory , @penaltyGracePeriod, @programID, @currencyID, @changeRate,         @loanState,  " + (loanObject.RequestDate.Equals(emptyDate) ? "NULL" : "@requestDate") + ", " + (loanObject.ContractDate.Equals(emptyDate) ? "NULL" : "@contractDate") + ", @loanManager )";

                    Hashtable parameters = new Hashtable();
                    parameters.Add("@loanID", loanObject.LoanID);
                    parameters.Add("@version", loanObject.LoanVersion);
                    parameters.Add("@productID", loanObject.ProductID);
                    parameters.Add("@clientID", loanObject.ClientID);
                    parameters.Add("@loanStartDate", loanObject.LoanStartDate.Date);
                    parameters.Add("@loanEndDate", loanObject.LoanEndDate.Date);
                    parameters.Add("@payPeriodID", loanObject.PayPeriodID);
                    parameters.Add("@fundingLineID", loanObject.FundingLineID);
                    parameters.Add("@creditAmountValue", loanObject.ContractAmount);
                    parameters.Add("@nrOfCreditMonthValue", loanObject.NrOfCreditMonth);
                    parameters.Add("@percentValue", loanObject.ContrcatPercent);
                    parameters.Add("@calculatePercentFrom", loanObject.CalculatePercentFrom);
                    parameters.Add("@penaltyValue", loanObject.ContractPenalty);
                    parameters.Add("@calculatePenaltyFrom", loanObject.CalculatePenaltyFrom);
                    parameters.Add("@loanScope", loanObject.LoanScope);
                    parameters.Add("@modifiedBy", loanObject.ModifiedBy);
                    parameters.Add("@contractCode", loanObject.ContractCode);
                    parameters.Add("@loanCategory", loanObject.LoanCategoryID);
                    parameters.Add("@penaltyGracePeriod", loanObject.PenaltyGracePeriod);
                    parameters.Add("@programID", loanObject.ProgramID);
                    parameters.Add("@currencyID", loanObject.CurrencyID);
                    parameters.Add("@changeRate", loanObject.CurrencyChangeRate);
                    parameters.Add("@loanState", loanObject.LoanState);
                    if (!loanObject.RequestDate.Equals(emptyDate)) parameters.Add("@requestDate", loanObject.RequestDate);
                    if (!loanObject.ContractDate.Equals(emptyDate)) parameters.Add("@contractDate", loanObject.ContractDate);
                    parameters.Add("@versionDate", loanObject.VersionDate);
                    parameters.Add("@loanManager", loanObject.LoanManagerID);

                    result = mDataBridge.ExecuteNonQuery(query, parameters);
                    mLastError = mDataBridge.LastError;
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool AddNewLoan(Credits.DataObjects.LoanObject loanObject)
        {
            bool result = false;
            mLastError = string.Empty;
            DateTime emptyDate = DateTime.MinValue;

            try
            {
                string query = string.Empty;

                query = "SELECT MAX(LoanID) FROM Loans ";
                object maxID = mDataBridge.ExecuteScalarQuery(query);
                mLastError = mDataBridge.LastError;

                if (maxID != null && !maxID.ToString().Equals(string.Empty))
                {
                    loanObject.LoanID = (int)maxID;
                }

                loanObject.LoanID += 1;
                loanObject.VersionDate = DateTime.Now;

                query = " INSERT INTO Loans (loanID,  version,  versionDate,  productID,  clientID, loanStartDate,   loanEndDate,    payPeriodID,  fundingLineID,  creditAmountValue,  nrOfCreditMonthValue, percentValue,   calculatePercentFrom,  penaltyValue,  calculatePenaltyFrom,  loanScope,  modifiedBy,  contractCode,  loanCategory, penaltyGracePeriod,    programID,  currencyID,  currencyChangeRate,  loanState,                        requestDate,                                                                              contractDate,                                  loanManager) "
                                + " VALUES (@loanID, @version, @versionDate, @productID, @clientID, @loanStartDate,  @loanEndDate , @payPeriodID, @fundingLineID, @creditAmountValue, @nrOfCreditMonthValue, @percentValue, @calculatePercentFrom, @penaltyValue, @calculatePenaltyFrom, @loanScope, @modifiedBy, @contractCode, @loanCategory , @penaltyGracePeriod, @programID, @currencyID, @changeRate,         @loanState,  " + (loanObject.RequestDate.Equals(emptyDate) ? "NULL" : "@requestDate") + ", " + (loanObject.ContractDate.Equals(emptyDate) ? "NULL" : "@contractDate") + ", @loanManager )";

                Hashtable parameters = new Hashtable();
                parameters.Add("@loanID", loanObject.LoanID);
                parameters.Add("@version", loanObject.LoanVersion);
                parameters.Add("@productID", loanObject.ProductID);
                parameters.Add("@clientID", loanObject.ClientID);
                parameters.Add("@loanStartDate", loanObject.LoanStartDate.Date);
                parameters.Add("@loanEndDate", loanObject.LoanEndDate.Date);
                parameters.Add("@payPeriodID", loanObject.PayPeriodID);
                parameters.Add("@fundingLineID", loanObject.FundingLineID);
                parameters.Add("@creditAmountValue", loanObject.ContractAmount);
                parameters.Add("@nrOfCreditMonthValue", loanObject.NrOfCreditMonth);
                parameters.Add("@percentValue", loanObject.ContrcatPercent);
                parameters.Add("@calculatePercentFrom", loanObject.CalculatePercentFrom);
                parameters.Add("@penaltyValue", loanObject.ContractPenalty);
                parameters.Add("@calculatePenaltyFrom", loanObject.CalculatePenaltyFrom);
                parameters.Add("@loanScope", loanObject.LoanScope);
                parameters.Add("@modifiedBy", loanObject.ModifiedBy);
                parameters.Add("@contractCode", loanObject.ContractCode);
                parameters.Add("@loanCategory", loanObject.LoanCategoryID);
                parameters.Add("@penaltyGracePeriod", loanObject.PenaltyGracePeriod);
                parameters.Add("@programID", loanObject.ProgramID);
                parameters.Add("@currencyID", loanObject.CurrencyID);
                parameters.Add("@changeRate", loanObject.CurrencyChangeRate);
                parameters.Add("@loanState", loanObject.LoanState);
                if (!loanObject.RequestDate.Equals(emptyDate)) parameters.Add("@requestDate", loanObject.RequestDate);
                if (!loanObject.ContractDate.Equals(emptyDate)) parameters.Add("@contractDate", loanObject.ContractDate);
                parameters.Add("@versionDate", loanObject.VersionDate);
                parameters.Add("@loanManager", loanObject.LoanManagerID);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }
        
        public bool DeleteLoan(int loanID)
        {
            bool result = false;
            try
            {
                string deleteQuery = "DELETE FROM LoanOrderChanges WHERE loanID = " + loanID;
                result = mDataBridge.ExecuteNonQuery(deleteQuery); // PG compliant
                mLastError = mDataBridge.LastError;

                deleteQuery = "DELETE FROM LoanOrders WHERE loanID = " + loanID;
                result = mDataBridge.ExecuteNonQuery(deleteQuery); // PG compliant
                mLastError = mDataBridge.LastError;

                deleteQuery = "DELETE FROM Loans WHERE loanID = " + loanID;
                result = mDataBridge.ExecuteNonQuery(deleteQuery); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        //public bool UpdateLoanEndDate(int loanID, DateTime loanEndDate, int modifiedUserID)
        //{
        //    bool result = false;
        //    DateTime emptyDate = DateTime.MinValue;

        //    try
        //    {
        //        int version = 0;

        //        string query = "SELECT MAX(version) FROM Loans WHERE loanID = " + loanID;
        //        object maxID = mDataBridge.ExecuteScalarQuery(query);
        //        mLastError = mDataBridge.LastError;

        //        if (maxID != null && !maxID.ToString().Equals(string.Empty))
        //        {
        //            version = (int)maxID;
        //            version += 1;
        //        }

        //        if (maxID != null && version != 0)
        //        {
        //            string nonQuery = @"INSERT INTO Loans (loanID, version, versionDate, productID, clientID, loanStartDate,                         loanEndDate,                                payPeriodID, fundingLineID, creditAmountValue, nrOfCreditMonthValue, percentValue, calculatePercentFrom, penaltyValue, calculatePenaltyFrom, penaltyGracePeriod, loanScope,     modifiedBy,        contractCode, loanCategory, programID, currencyID, currencyChangeRate, loanState, requestDate, contractDate)"
        //                               + " SELECT loanID, " + version + ", @dateTimeNow, productID, clientID, loanStartDate , " + (loanEndDate.Equals(emptyDate) ? "NULL" : "@loanEndDate") + ", payPeriodID, fundingLineID, creditAmountValue, nrOfCreditMonthValue, percentValue, calculatePercentFrom, penaltyValue, calculatePenaltyFrom, penaltyGracePeriod, loanScope, " + modifiedUserID + ", contractCode, loanCategory, programID, currencyID, currencyChangeRate, loanState, requestDate, contractDate "
        //                                  + " FROM Loans WHERE loanID = " + loanID + " AND version = " + (int)maxID;

        //            Hashtable paraeters = new Hashtable();
        //            paraeters.Add("@dateTimeNow", DateTime.Now);
        //            if (!loanEndDate.Equals(emptyDate)) paraeters.Add("@loanEndDate", loanEndDate);

        //            result = mDataBridge.ExecuteNonQuery(nonQuery, paraeters); // PG compliant
        //            mLastError = mDataBridge.LastError;
        //        }
        //    }
        //    catch (Exception exception)
        //    {
        //        mLastError += "Error using DataBridge. " + exception.Message;
        //    }

        //    return result;
        //}

        //public bool UpdateLoanAproveStatus(int loanID, int loanState, int modifiedUserID)
        //{
        //    bool result = false;
        //    DateTime emptyDate = DateTime.MinValue;

        //    try
        //    {
        //        int version = 0;

        //        string query = "SELECT MAX(version) FROM Loans WHERE loanID = " + loanID;
        //        object maxID = mDataBridge.ExecuteScalarQuery(query);
        //        mLastError = mDataBridge.LastError;

        //        if (maxID != null && !maxID.ToString().Equals(string.Empty))
        //        {
        //            version = (int)maxID;
        //            version += 1;
        //        }

        //        if (maxID != null && version != 0)
        //        {
        //            string nonQuery = @"INSERT INTO Loans (loanID, version, versionDate, productID, clientID, loanStartDate, loanEndDate, payPeriodID, fundingLineID, creditAmountValue, nrOfCreditMonthValue, percentValue, calculatePercentFrom, penaltyValue, calculatePenaltyFrom, penaltyGracePeriod, loanScope,       modifiedBy,        contractCode, loanCategory, programID, currencyID, currencyChangeRate, loanState, requestDate, contractDate, loanManager)"
        //                               + " SELECT loanID, " + version + ", @dateTimeNow, productID, clientID, loanStartDate ,loanEndDate, payPeriodID, fundingLineID, creditAmountValue, nrOfCreditMonthValue, percentValue, calculatePercentFrom, penaltyValue, calculatePenaltyFrom, penaltyGracePeriod, loanScope,  " + modifiedUserID + ", contractCode, loanCategory, programID, currencyID, currencyChangeRate, " + loanState + ", requestDate, contractDate, loanManager "
        //                                  + " FROM Loans WHERE loanID = " + loanID + " AND version = " + (int)maxID;

        //            Hashtable parameters = new Hashtable();
        //            parameters.Add("@dateTimeNow", DateTime.Now);

        //            result = mDataBridge.ExecuteNonQuery(nonQuery, parameters); // PG compliant
        //            mLastError = mDataBridge.LastError;
        //        }
        //    }
        //    catch (Exception exception)
        //    {
        //        mLastError += "Error using DataBridge. " + exception.Message;
        //    }

        //    return result;
        //}

        public bool MarkLoanAsFinishedIfAllTransheFinished(Credits.DataObjects.LoanObject loanObject)
        {
            bool result = false;

            try
            {
                if(loanObject != null ) 
                {
                    string query = "Select * FROM LoanOrders WHERE loanID = " + loanObject.LoanID + " AND finishedTranche = @False ";

                    Hashtable parameter = new Hashtable();
                    parameter.Add("@False", false);

                    DataTable notfinishedTranshe = mDataBridge.ExecuteQuery(query, parameter); // PG compliant
                    mLastError = mDataBridge.LastError;

                    if (notfinishedTranshe != null && notfinishedTranshe.Rows.Count == 0)
                    {
                        result = true;

                        loanObject.LoanState = (int)Constants.Constants.Classifiers.CreditState_Achitat;
                        UpdateLoan(loanObject);
                    }
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        #endregion Client Loans

        #region Operators

        public DataTable GetOperatorsList()
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT "
                            + "  OperatorID "
                            + " , Nume "
                            + " , Prenume "
                            + " , coalesce(Nume, '') " + Plus + " coalesce(Prenume, '') as \"Operator Full Name\" "
                            + " , genID "
                            + " , (Select Name From Classifiers Where Code = genID ) as genID_string "
                            + " , Specializare "
                            + " , Adresa "
                            + " , TelefonFix "
                            + " , TelefonGSM "
                            + " , Email "
                            + " , Organizatie "

                            + " FROM Operators ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetOperatorByID(int OperatorID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT "
                            + "  OperatorID "
                            + " , Nume "
                            + " , Prenume "
                            + " , genID "
                            + " , (Select Name From Classifiers Where Code = genID ) as genID_string "
                            + " , Specializare "
                            + " , Adresa "
                            + " , TelefonFix "
                            + " , TelefonGSM "
                            + " , Email "
                            + " , Organizatie "

                            + " FROM Operators "
                            + " WHERE OperatorID = " + OperatorID;

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateOperator(int operatorID, string Nume, string Prenume, int genID, string Specializare, string Adresa, string TelefonFix, string TelefonGSM, string Email, string Organizatie)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " UPDATE  Operators SET "
                                + "  Nume = '" + Nume + "' "
                                + " , Prenume = '" + Prenume + "' "
                                + " , genID = " + genID
                                + " , Specializare = '" + Specializare + "' "
                                + " , Adresa = '" + Adresa + "' "
                                + " , TelefonFix = '" + TelefonFix + "' "
                                + " , TelefonGSM = '" + TelefonGSM + "' "
                                + " , Email = '" + Email + "' "
                                + " , Organizatie = '" + Organizatie + "' "

                                + " WHERE OperatorID = " + operatorID;

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool DeleteOperator(int operatorID)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " Delete From Operators  WHERE OperatorID = " + operatorID;

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool AddNewOperator(string Nume, string Prenume, int genID, string Specializare, string Adresa, string TelefonFix, string TelefonGSM, string Email, string Organizatie)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " INSERT INTO Operators ( Nume,           Prenume,          genID,         Specializare,          Adresa,           TelefonFix,           TelefonGSM,           Email,           Organizatie ) "
                                        + " Values ('" + Nume + "', '" + Prenume + "', " + genID + ", '" + Specializare + "', '" + Adresa + "', '" + TelefonFix + "', '" + TelefonGSM + "', '" + Email + "', '" + Organizatie + "' ) ";

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        #endregion Operators

        #region Bank Region

        public bool UpdateClientBank(int clientID, int bankID, string clientContBancar)
        {
            bool result = false;

            try
            {
                string query = "UPDATE Client SET bank_id = " + bankID + ", contbancar = '" + clientContBancar + "' WHERE clientID =  " + clientID;
                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        #endregion Bank Region

        #region Loan Orders

        //public void TestLoanOrdersForDelayedPays(int loanID)
        //{
        //    DataTable loanInfo = this.GetLoanByID(loanID);
        //    if (loanInfo != null && loanInfo.Rows.Count == 1 && loanInfo.Rows[0]["loanCategory"] != System.DBNull.Value && (int)loanInfo.Rows[0]["loanCategory"] == (int)Constants.Constants.Classifiers.ContractCategory_Normal)
        //    {
        //        int calculatePenaltyFrom = 0;
        //        if (loanInfo.Rows[0]["calculatePenaltyFrom"] != System.DBNull.Value) calculatePenaltyFrom = (int)loanInfo.Rows[0]["calculatePenaltyFrom"];

        //        decimal penaltyValue = 0;
        //        if (loanInfo.Rows[0]["penaltyValue"] != System.DBNull.Value) penaltyValue = (decimal)loanInfo.Rows[0]["penaltyValue"];

        //        int initialAmount = (int)loanInfo.Rows[0]["creditAmountValue"];

        //        int penaltyGracePeriod = 0;
        //        int.TryParse(loanInfo.Rows[0]["penaltyGracePeriod"].ToString(), out penaltyGracePeriod);

        //        if (calculatePenaltyFrom != 0 && penaltyValue != 0)
        //        {
        //            DataTable loanOrders = null;
        //            try
        //            {
        //                string query = " SELECT * FROM LoanOrders WHERE LoanID = " + loanID + " order by calculatedData asc ";

        //                loanOrders = mDataBridge.ExecuteQuery(query);
        //                mLastError = mDataBridge.LastError;
        //            }
        //            catch (Exception exception)
        //            {
        //                mLastError += "Error using DataBridge. " + exception.Message;
        //            }

        //            if (loanOrders != null && loanOrders.Rows.Count > 0)
        //            {
        //                for (int i = 0; i < loanOrders.Rows.Count; i++)
        //                {
        //                    DateTime calculatedData = (DateTime)loanOrders.Rows[i]["calculatedData"];
        //                    DateTime payedDate = DateTime.MinValue;

        //                    if (loanOrders.Rows[i]["payedDate"] == System.DBNull.Value && calculatedData.AddDays(penaltyGracePeriod) < DateTime.Now)
        //                    {
        //                        decimal percentValue = 0;
        //                        decimal.TryParse(loanOrders.Rows[i]["percentValue"].ToString(), out percentValue);

        //                        decimal mainRate = 0;
        //                        decimal.TryParse(loanOrders.Rows[i]["mainRate"].ToString(), out mainRate);

        //                        int iterationID = (int)loanOrders.Rows[i]["iterationID"];
        //                        decimal remainingAmount = (decimal)loanOrders.Rows[i]["remainingAmount"];

        //                        int delayDaysCount = (DateTime.Now - calculatedData).Days;

        //                        switch (calculatePenaltyFrom)
        //                        {
        //                            case (int)Constants.Constants.Classifiers.CalculatePenalty_DeLaSumaIntirzierii:
        //                                {
        //                                    decimal penaltyAmount = ((percentValue + mainRate) * penaltyValue) / 100 * delayDaysCount;
        //                                    string penaltyComment = "Intirzierea de " + delayDaysCount + " zile";
        //                                    this.UpdateLoanOrder(loanID, iterationID, calculatedData, percentValue, mainRate, remainingAmount, penaltyAmount, penaltyComment);
        //                                }
        //                                break;

        //                            case (int)Constants.Constants.Classifiers.CalculatePenalty_DinSumaInitiala:
        //                                {
        //                                    decimal penaltyAmount = (initialAmount * penaltyValue) / 100 * delayDaysCount;
        //                                    string penaltyComment = "Intirzierea de " + delayDaysCount + " zile";
        //                                    this.UpdateLoanOrder(loanID, iterationID, calculatedData, percentValue, mainRate, remainingAmount, penaltyAmount, penaltyComment);
        //                                }
        //                                break;

        //                            case (int)Constants.Constants.Classifiers.CalculatePenalty_SumaFixa:
        //                                {
        //                                    decimal penaltyAmount = penaltyValue * delayDaysCount;
        //                                    string penaltyComment = "Intirzierea de " + delayDaysCount + " zile";
        //                                    this.UpdateLoanOrder(loanID, iterationID, calculatedData, percentValue, mainRate, remainingAmount, penaltyAmount, penaltyComment);
        //                                }
        //                                break;

        //                            default:
        //                                break;
        //                        }
        //                    }
        //                }
        //            }
        //        }
        //    }
        //}

        //public bool AdjustLoanOrdersChangeRate(int loanID, int currencyID)
        //{
        //    bool result = false;
        //    mLastError = string.Empty;

        //    try
        //    {
        //        string query = " UPDATE LoanOrders \r\n "
        //                + " SET \r\n "
        //                + "     LoanOrders.currencyChangeRate = RateHistroy.rate   \r\n "
        //                + " FROM \r\n "
        //                + "     LoanOrders \r\n "
        //                + "  JOIN \r\n "
        //                + "     RateHistroy \r\n "
        //                + " ON \r\n "
        //                + "     LoanOrders.calculatedData = RateHistroy.data \r\n "
        //                + " WHERE \r\n "
        //                + " LoanOrders.LoanID = " + loanID + " \r\n "
        //                + " AND RateHistroy.currency = " + currencyID + " \r\n ";

        //        result = mDataBridge.ExecuteNonQuery(query);
        //        mLastError = mDataBridge.LastError;
        //    }
        //    catch (Exception exception)
        //    {
        //        mLastError += "Error using DataBridge. " + exception.Message;
        //    }

        //    return result;
        //}

        public DataTable GetLoanOrderList(int loanID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT ROW_NUMBER() OVER(Order By calculatedData) AS \"iterationID\", \r\n "
                                    + " LoanID  \r\n "
                                    + " , iterationID  \r\n "
                                    + " , calculatedData  \r\n "
                                    + " , days \r\n  "
                                    + " , percentValue  \r\n "
                                    + " , mainRate  \r\n "
                                    + " , percentValue " + mDataBridge.ConcatSimbol + " mainRate  as \"totalAmount\"  \r\n "
                                    + " , remainingAmount  \r\n "
                                    + " , (CASE WHEN currencyChangeRate > 0 THEN cast((percentValue " + mDataBridge.ConcatSimbol + " mainRate ) / currencyChangeRate as decimal(10,2)) ELSE 0 END) as \"totalAmount_Rated\" \r\n  "
                                    + " , currencyChangeRate  \r\n "
                                    + " , mainRate_Rated - mainRate as corectiaLaCurs  \r\n "
                                    + " , finishedTranche  \r\n "
                                    + " , manualmodifed  \r\n "
                                    + " , mainRate_Rated  \r\n "

                                    + " FROM LoanOrders WHERE LoanID = " + loanID + " \r\n "
                                    + " order by calculatedData asc  \r\n ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool AddNewLoanOrder(int LoanID, int iterationID, DateTime calculatedData, int days, decimal percentValue, decimal mainRate, decimal remainingAmount, decimal currencyChangeRate, bool manualmodifed)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " INSERT INTO LoanOrders (LoanID, iterationID,  calculatedData, days, percentValue, mainRate, remainingAmount, currencyChangeRate, manualmodifed) "
                    + " VALUES (" + LoanID + "," + iterationID + ", @calculatedData, @days, @percentValue, @mainRate, @remainingAmount, @currencyChangeRate, @manualmodifed)";

                Hashtable parameters = new Hashtable();
                parameters.Add("@calculatedData", calculatedData.Date);
                parameters.Add("@days", days);
                parameters.Add("@percentValue", percentValue);
                parameters.Add("@mainRate", mainRate);
                parameters.Add("@remainingAmount", remainingAmount);
                parameters.Add("@currencyChangeRate", currencyChangeRate);
                parameters.Add("@manualmodifed", manualmodifed);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        //public bool AddNewLoanOrder(int LoanID, int iterationID, DateTime calculatedData, decimal percentValue, decimal percentValue_Rated, decimal mainRate, decimal mainRate_Rated, decimal remainingAmount, decimal currencyChangeRate, decimal additionalPays, string additionalPaysComment, DateTime payedDate, string payedOrderNumber)
        //{
        //    bool result = false;
        //    mLastError = string.Empty;
        //    DateTime emptyDate = DateTime.MinValue;

        //    try
        //    {
        //        string query = " INSERT INTO LoanOrders (LoanID, iterationID,  calculatedData, percentValue, percentValue_Rated , mainRate, mainRate_Rated, remainingAmount, currencyChangeRate, additionalPays, additionalPaysComment  , payedDate , payedOrderNumber ) "
        //            + " VALUES (" + LoanID + "," + iterationID + ", @calculatedData , @percentValue, @percentValue_Rated, @mainRate, @mainRate_Rated, @remainingAmount, @currencyChangeRate, @additionalPays, @additionalPaysComment , " + (payedDate.Equals(emptyDate) ? "NULL" : "@payedDate") + ", @payedOrderNumber )";

        //        Hashtable parameters = new Hashtable();
        //        parameters.Add("@calculatedData", calculatedData.Date);
        //        parameters.Add("@percentValue", percentValue);
        //        parameters.Add("@mainRate", mainRate);
        //        parameters.Add("@remainingAmount", remainingAmount);
        //        parameters.Add("@percentValue_Rated", percentValue_Rated);
        //        parameters.Add("@mainRate_Rated", mainRate_Rated);
        //        parameters.Add("@currencyChangeRate", currencyChangeRate);
        //        parameters.Add("@additionalPays", additionalPays);
        //        parameters.Add("@additionalPaysComment", additionalPaysComment);
        //        parameters.Add("@payedOrderNumber", payedOrderNumber);
        //        if (!payedDate.Equals(emptyDate)) parameters.Add("@payedDate", payedDate);

        //        result = mDataBridge.ExecuteNonQuery(query, parameters);
        //        mLastError = mDataBridge.LastError;
        //    }
        //    catch (Exception exception)
        //    {
        //        mLastError += "Error using DataBridge. " + exception.Message;
        //    }

        //    return result;
        //}

        //public bool AddCommentForUpdateLoanOrder(int loanID, int iterationId, int changetByID, string comment)
        //{
        //    bool result = false;
        //    mLastError = string.Empty;

        //    try
        //    {
        //        string query = " INSERT INTO LoanOrderChanges ( LoanID, iterationId, ChangetDate, ChangetBy, Comment) "
        //                    + " VALUES ( " + loanID + ", " + iterationId + ", @changetDate , " + changetByID + ", '" + comment + "') ";

        //        Hashtable parameters = new Hashtable();
        //        parameters.Add("@changetDate", DateTime.Now);

        //        result = mDataBridge.ExecuteNonQuery(query, parameters);
        //        mLastError = mDataBridge.LastError;
        //    }
        //    catch (Exception exception)
        //    {
        //        mLastError += "Error using DataBridge. " + exception.Message;
        //    }

        //    return result;
        //}

        public bool UpdateLoanOrder(int loanID, int iterationID, DateTime calculatedData, int days, decimal percentValue, decimal mainRate, decimal remainingAmount)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " UPDATE LoanOrders SET "
                            + " calculatedData = @calculatedData "
                            + " days = @days "
                            + " , percentValue = @percentValue "
                            + " , mainRate = @mainRate "
                            + " , remainingAmount = @remainingAmount "

                            + " WHERE LoanID = " + loanID + " AND iterationID = " + iterationID;

                Hashtable parameters = new Hashtable();
                parameters.Add("@calculatedData", calculatedData);
                parameters.Add("@days", days);
                parameters.Add("@percentValue", percentValue);
                parameters.Add("@mainRate", mainRate);
                parameters.Add("@remainingAmount", remainingAmount);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateLoanOrderPays(int loanID, int iterationID, DateTime payedData, decimal percentValue, decimal mainRate, decimal remainingAmount, decimal additionalPays, string additionalPaysComment, decimal insertPays, decimal difPayment, decimal cursMDL)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " UPDATE LoanOrders SET "
                            + " payedDate = @payedData "
                            + " , percentValue_Pays = @percentValue "
                            + " , mainRate_Pays = @mainRate "
                            + " , remainingAmount = @remainingAmount "
                            + " , additionalPays = @additionalPays "
                            + " , additionalPaysComment = '" + additionalPaysComment + "' "
                            + " , input_Pays = @inputPays "
                            + " , diference_Pays = @difPays "
                            + " , currencyChangeRate = @cursMDL "
                            + " WHERE LoanID = " + loanID + " AND iterationID = " + iterationID;

                Hashtable parameters = new Hashtable();
                parameters.Add("@payedData", payedData);
                parameters.Add("@percentValue", percentValue);
                parameters.Add("@mainRate", mainRate);
                parameters.Add("@remainingAmount", remainingAmount);
                parameters.Add("@additionalPays", additionalPays);
                parameters.Add("@inputPays", insertPays);
                parameters.Add("@difPays", difPayment);
                parameters.Add("@cursMDL", cursMDL);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateLoanOrder(int loanID, int iterationID, DateTime payedDate, string payedOrderNumber)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " UPDATE LoanOrders SET "
                            + " payedDate = @payedDate "
                            + " , payedOrderNumber = @payedOrderNumber "
                            + " WHERE LoanID = " + loanID + " AND iterationID = " + iterationID;

                Hashtable parameters = new Hashtable();
                parameters.Add("@payedDate", payedDate);
                parameters.Add("@payedOrderNumber", payedOrderNumber);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool ClearLoanOrderForLoanID(int loanID)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " DELETE From LoanOrders WHERE LoanID = " + loanID;

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool ApplyNewChangeRateForloanOrders(int loanID, decimal newChangeRate, DateTime fromDateToAply)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " UPDATE LoanOrders SET "
                            + " currencyChangeRate = @newChangeRate "
                            + " , mainRate_Rated = mainRate * @newChangeRate "
                            + " , percentValue_Rated = percentValue * @newChangeRate "
                            + " WHERE LoanID = " + loanID + " AND calculatedData >= @fromDateToAply";

                Hashtable parameters = new Hashtable();
                parameters.Add("@newChangeRate", newChangeRate);
                parameters.Add("@fromDateToAply", fromDateToAply);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetRefundsLoanOrderDT1(int loanID)
        {
            DataTable resultDT = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT LoanID "
                    + ", iterationID+1 as iterationID "
                    + ", days "
                    + ", [calculatedData] AS DateID "
                    + ", (select MAX(LOP.payDate) from LoanOrdersPays as LOP where LOP.LoanID=LoanOrders.LoanID and LOP.iterationID=LoanOrders.iterationID ) as PaidDate "
                    + ", [finishedTranche] as isFinID "
                    + ", [mainRate] as principalDueID "
                    + ", 0 as balanceOfLoan "
                    + ", percentValue as interestID "
                    + ", (select sum(LOP.commission) from LoanOrdersPays as LOP where LOP.LoanID=LoanOrders.LoanID and LOP.iterationID=LoanOrders.iterationID ) as feesID "   //[commission]
                    + ", (select sum(LOP.penalty) from LoanOrdersPays as LOP where LOP.LoanID=LoanOrders.LoanID and LOP.iterationID=LoanOrders.iterationID ) AS penaltiesID "
                    + ", [currencyChangeRate] AS currenty "
                    + ", mainRate + percentValue as dueID "
                    + ", (select sum(LOP.main)+sum(LOP.profit)+sum(LOP.penalty)+sum(LOP.commission) from LoanOrdersPays as LOP where LOP.LoanID=LoanOrders.LoanID and LOP.iterationID=LoanOrders.iterationID) as paidID "
                    + ", (select sum(LOP.avans) from LoanOrdersPays as LOP where LOP.LoanID=LoanOrders.LoanID and LOP.iterationID=LoanOrders.iterationID) as inAdvanceID "
                    + ", (select sum(LOP.late) from LoanOrdersPays as LOP where LOP.LoanID=LoanOrders.LoanID and LOP.iterationID=LoanOrders.iterationID) as lateID "
                    + ", 0 as outstandingID " 
                    + ", (select sum(LOP.main) from LoanOrdersPays as LOP where LOP.LoanID=LoanOrders.LoanID and LOP.iterationID=LoanOrders.iterationID) as mainPays "
                    + ", (select sum(LOP.profit) from LoanOrdersPays as LOP where LOP.LoanID=LoanOrders.LoanID and LOP.iterationID=LoanOrders.iterationID) as profitPays "
                    + " FROM [LoanOrders] where LoanID = " + loanID

                    + " ORDER BY LoanID, iterationID ";

                resultDT = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
                if (resultDT != null && resultDT.Rows.Count > 0)
                {
                    DataRow newRow = resultDT.NewRow();
                    DataRow footerRow = resultDT.NewRow();

                    decimal balance = 0;
                    Credits.DataObjects.LoanObject loanInfo = GetLoanObjectByID(loanID);
                    if (loanInfo != null)
                    {
                        balance = loanInfo.ContractAmount;
                        DateTime contractDate = loanInfo.ContractDate;
                        newRow["balanceOfLoan"] = balance;
                        newRow["DateID"] = contractDate;
                    }

                    int fDays = 0;
                    decimal fPrincipalDue = 0;
                    decimal fInterest = 0;
                    decimal fFees = 0;
                    decimal fPenaltiesID = 0;
                    decimal fDueID = 0;
                    decimal fPaidID = 0;
                    decimal fInAdvanceID = 0;
                    decimal fLateID = 0;
                    decimal fOutstandingID = 0;
                    decimal fMainPays = 0;
                    decimal fProfitPays = 0;

                    for (int i = 0; i < resultDT.Rows.Count; i++)
                    {
                        string principalDueValue = resultDT.Rows[i]["principalDueID"].ToString();
                        string interestValue = resultDT.Rows[i]["interestID"].ToString();
                        decimal principal = 0;
                        decimal interest = 0;
                        decimal.TryParse(principalDueValue, out principal);
                        decimal.TryParse(interestValue, out interest);

                        balance = balance - principal;
                        resultDT.Rows[i]["balanceOfLoan"] = balance;

                        #region count items for footer
                        int mDays = 0;
                        int.TryParse(resultDT.Rows[i]["days"].ToString(), out mDays);
                        fDays += mDays;

                        fPrincipalDue += principal;

                        fInterest += interest;

                        decimal mFees = 0;
                        decimal.TryParse(resultDT.Rows[i]["feesID"].ToString(), out mFees);
                        fFees += mFees;

                        decimal mPenaltiesID = 0;
                        decimal.TryParse(resultDT.Rows[i]["penaltiesID"].ToString(), out mPenaltiesID);
                        fPenaltiesID += mPenaltiesID;

                        decimal mDueID = 0;
                        decimal.TryParse(resultDT.Rows[i]["dueID"].ToString(), out mDueID);
                        fDueID += mDueID;

                        decimal mPaidID = 0;
                        decimal.TryParse(resultDT.Rows[i]["paidID"].ToString(), out mPaidID);
                        fPaidID += mPaidID;

                        decimal mInAdvanceID = 0;
                        decimal.TryParse(resultDT.Rows[i]["inAdvanceID"].ToString(), out mInAdvanceID);
                        fInAdvanceID += mInAdvanceID;

                        decimal mLateID = 0;
                        decimal.TryParse(resultDT.Rows[i]["lateID"].ToString(), out mLateID);
                        fLateID += mLateID;

                        resultDT.Rows[i]["outstandingID"] = mDueID - mPaidID + mPenaltiesID;

                        decimal mOutstandingID = mDueID - mPaidID + mPenaltiesID;

                        fOutstandingID += mOutstandingID;

                        decimal mMainPays = 0;
                        decimal.TryParse(resultDT.Rows[i]["mainPays"].ToString(), out mMainPays);
                        fMainPays += mMainPays;

                        decimal mProfitPays = 0;
                        decimal.TryParse(resultDT.Rows[i]["profitPays"].ToString(), out mProfitPays);
                        fProfitPays += mProfitPays;

                        #endregion
                    }

                    footerRow["days"] = fDays;
                    footerRow["principalDueID"] = fPrincipalDue;
                    footerRow["interestID"] = fInterest;
                    footerRow["feesID"] = fFees;
                    footerRow["penaltiesID"] = fPenaltiesID;
                    footerRow["dueID"] = fDueID;
                    footerRow["paidID"] = fPaidID;
                    footerRow["inAdvanceID"] = fInAdvanceID;
                    footerRow["lateID"] = fLateID;
                    footerRow["outstandingID"] = fOutstandingID;
                    footerRow["mainPays"] = fMainPays;
                    footerRow["profitPays"] = fProfitPays;

                    int myPositioni = 0;
                    resultDT.Rows.InsertAt(newRow, myPositioni);
                    resultDT.Rows.InsertAt(footerRow, resultDT.Rows.Count);
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return resultDT;
        }

        public DataTable GetTranzactionsLoanOrderDT(int loanID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT [paysID] as TranzactionID"
                    + ", 'Head Office' as TranzactionsOffice"
                    + ", [payDate] as TranzactionDate "
                    + ", 'Repayment' as Type "
                    + ", isnull([main],0) + isnull([profit],0)+isnull([penalty],0) + isnull([commission],0) as Amount "
                    + ", isnull([main], 0) as Principal "
                    + ", isnull([profit], 0) as Interest "
                    + ", isnull([commission], 0) as Fees "
                    + ", isnull([penalty], 0) as Penalties "
                    + ", 1.0000 as Rate "
                    + ", isPenalty "
                    + ", isnull([avans],0) as Avans "
                    + " FROM [LoanOrdersPays] "
                    + " WHERE LoanID = " + loanID
                    //+ " AND parentID = 0 "
                    + " ORDER BY [payDate] DESC, iterationID DESC ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetRefundsLoanOrderList(int loanID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT LoanID "
                    + ", iterationID "
                    + ", calculatedData AS Data "
                    + ", [mainRate] AS main "
                    + ", [percentValue] AS profit "
                    + ", 0 AS penalty "
                    + ", [currencyChangeRate] AS currenty "
                    + ", 0 as [commission] "
                    + ", 0 as PaysID "
                    + ", 0 as Avans "
                    + ", 0 as parentID "
                    + ", '-' as toogleID "
                    + ", isnull([mainRate],0)+isnull([percentValue],0) as totalByIteration "
                    + ", [finishedTranche] as isFinis "
                    + " FROM [LoanOrders] where LoanID = " + loanID
                    + " UNION "
                    + " SELECT [LoanID] "
                    + ", [iterationID] "
                    + ", [payDate] as Data "
                    + ", [main] "
                    + ", [profit] "
                    + ", [penalty] "
                    + ", [currenty] "
                    + ", [commission] "
                    + ", [PaysID] "
                    + ", [Avans] "
                    + ", [parentID] "
                    + ", '' as toogleID "
                    + ", isnull([main],0) + isnull([profit],0)+isnull([penalty],0) as totalByIteration "
                    + ", 0 as isFinis "
                    + " FROM [LoanOrdersPays] where LoanID = " + loanID
                    + " ORDER BY LoanID, iterationID, PaysID ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool AddNewRefund(int loanID, int iterationID, DateTime payDate, decimal main, decimal profit, decimal penalty, decimal currenty, decimal commission, decimal avans, decimal late, int parentID, bool isPenalty, decimal lateValuta, out int payID)
        {
            bool result = false;
            payID = 0;
            try
            {
                string nonQuery = "INSERT INTO LoanOrdersPays (LoanID, iterationID, payDate, main, profit, penalty, currenty, commission, avans, parentID, late, isPenalty, lateValuta) "
                    + " OUTPUT INSERTED.PaysID "
                    + " Values (" + loanID + ", " + iterationID + ", @datePay, @main, @profit, @penalty , @currenty , @commission, @avans, " + parentID + ", @late, @isPenalty, @lateValuta ) ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@datePay", payDate);
                parameters.Add("@main", main);
                parameters.Add("@profit", profit);
                parameters.Add("@penalty", penalty);
                parameters.Add("@currenty", currenty);
                parameters.Add("@commission", commission);
                parameters.Add("@avans", avans);
                parameters.Add("@late", late);
                parameters.Add("@isPenalty", isPenalty);
                parameters.Add("@lateValuta", lateValuta);

                object insertedID = mDataBridge.ExecuteScalarQuery(nonQuery, parameters); // PG compliant
                mLastError = mDataBridge.LastError;

                if (insertedID != null && !insertedID.ToString().Equals(string.Empty))
                {
                    payID = (int)insertedID;
                    result = true;
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool DeleteRefundByPayID(int payID)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " DELETE FROM LoanOrdersPays "
                + " WHERE PaysID = " + payID
                + " OR parentID = " + payID
                + " OR PaysID IN (SELECT parentID FROM LoanOrdersPays WHERE PaysID = " + payID + ") "
                + " ";

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        //public bool AddAvansRefund(int loanID, int iterationID, DateTime payDate, decimal avans, int payID)
        //{
        //    bool result = false;
        //    try
        //    {
        //        DataTable refundTable = new DataTable();
        //        refundTable = GetRefundsLoanOrderList(loanID);
        //        if (refundTable != null)
        //        {
        //            iterationID = iterationID + 1;

        //            DataRow[] mSelectedRows = refundTable.Select("iterationID = " + iterationID);

        //            decimal profit = 0;
        //            decimal main = 0;

        //            for (int i = 0; i < mSelectedRows.Length; i++)
        //            {
        //                decimal mainBasic = 0;
        //                decimal profitBasic = 0;
        //                decimal.TryParse(mSelectedRows[i][3].ToString(), out mainBasic);
        //                decimal.TryParse(mSelectedRows[i][4].ToString(), out profitBasic);

        //                string pays = mSelectedRows[i][8].ToString();
        //                int paysID = 0;
        //                int.TryParse(pays, out paysID);
        //                if (paysID == 0)
        //                {
        //                    main += mainBasic;
        //                    profit += profitBasic;
        //                }
        //                else
        //                {
        //                    main -= mainBasic;
        //                    profit -= profitBasic;
        //                }
        //            }

        //            decimal currenty = 1;
        //            decimal commission = 0;

        //            int newPayID = 0;

        //            if (avans <= main + profit)
        //            {
        //                //                        
        //                if (avans >= profit)
        //                {
        //                    main = avans - profit;
        //                }
        //                else
        //                {
        //                    profit = avans;
        //                    main = 0;
        //                }

        //                AddNewRefund(loanID, iterationID, payDate, main, profit, 0, currenty, commission, 0, payID, out newPayID);
        //            }
        //            else
        //            {
        //                //
        //                AddNewRefund(loanID, iterationID, payDate, main, profit, 0, currenty, commission, 0, payID, out newPayID);

        //                avans = avans - profit - main;

        //                AddAvansRefund(loanID, iterationID, payDate, avans, payID);
        //            }
        //        }
        //        result = true;
        //    }
        //    catch (Exception ex)
        //    {
        //        mLastError += "Error using DataBridge. " + ex.Message;
        //    }

        //    return result;
        //}

        #endregion Loan Orders

        #region Vizits Consulting

        public DataTable GetVisitsConsultList()
        {
            DataTable resultTable = new DataTable();
            try
            {
                string commandText = @"SELECT * "
                        + " , (select coalesce(FirstName, '') " + Plus + " coalesce(LastName, '') From Client Where Client.clientID = ConsultEval.ClientID ) as \"Client Full Name\" "
                        + " , (select coalesce(Nume, '') " + Plus + " coalesce(Prenume, '') From Operators Where Operators.OperatorID = ConsultEval.OperatorID ) as \"Operator Full Name\" "
                        + " , (Select Name From Classifiers WHERE Classifiers.Code = TipConsultEval) as TipConsultEval_string "
                        + " , (Select Name From Classifiers WHERE Classifiers.Code = BusinessState) as BusinessState_string "

                        + " FROM ConsultEval ";

                resultTable = mDataBridge.ExecuteQuery(commandText); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return resultTable;
        }

        public DataTable GetVisitsConsultById(int clevID)
        {
            DataTable resultTable = new DataTable();
            try
            {
                string commandText = @"SELECT * "
                        + " , (select coalesce(FirstName, '') " + Plus + " coalesce(LastName, '') From Client Where Client.clientID = ConsultEval.ClientID ) as \"Client Full Name\" "
                        + " , (select coalesce(Nume, '') " + Plus + " coalesce(Prenume, '') From Operators Where Operators.OperatorID = ConsultEval.OperatorID ) as \"Operator Full Name\" "
                        + " , (Select Name From Classifiers WHERE Classifiers.Code = TipConsultEval) as TipConsultEval_string "
                        + " , (Select Name From Classifiers WHERE Classifiers.Code = BusinessState) as BusinessState_string "

                        + " FROM ConsultEval WHERE clevID = " + clevID;

                resultTable = mDataBridge.ExecuteQuery(commandText); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return resultTable;
        }

        public DataTable GetAllConsultsForClient(int clientID)
        {
            DataTable resultTable = new DataTable();
            try
            {
                string commandText = @"SELECT * "
                        + " , (select coalesce(Nume, '') " + Plus + " coalesce(Prenume, '') From Operators Where Operators.OperatorID = ConsultEval.OperatorID ) as \"Operator Full Name\" "
                        + " , (Select Name From Classifiers WHERE Classifiers.Code = TipConsultEval) as TipConsultEval_string "
                        + " , (Select Name From Classifiers WHERE Classifiers.Code = BusinessState) as BusinessState_string "
                        + " , (CASE WHEN "
                        + " (select COUNT(photoid) From ConsultEvalPhoto WHERE ConsultEvalPhoto.clevID = ConsultEval.clevID) > 0 "
                        + " THEN "
                        + " CAST((select COUNT(photoid) From ConsultEvalPhoto WHERE ConsultEvalPhoto.clevID = ConsultEval.clevID) as varchar) "
                        + " else "
                        + "  'Nu sunt fotografii' "
                        + " END "
                        + "  )  as \"fotocount\" "

                        + " FROM ConsultEval WHERE clientID = " + clientID;

                resultTable = mDataBridge.ExecuteQuery(commandText); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return resultTable;
        }

        public bool AddVizitConsult(DateTime consultDate, int clientID, int operatorID, int tipConsultEvalID, string descriereConsult, int businessStateID, int nrDeAngajati, int venitMediuLunar, string shortDescription, out int clevID)
        {
            bool result = false;
            clevID = 0;
            try
            {
                string nonQuery = @"INSERT INTO ConsultEval (data, clientID, OperatorID, TipConsultEval, ConsultDescription, BusinessState, employeesCount, AverageMonthlyIncome, shortDescription)"
                    + " OUTPUT INSERTED.ClevID "
                     + " VALUES ( @consultDate," + clientID + ", " + operatorID + "," + tipConsultEvalID + ", '" + descriereConsult + "', " + businessStateID + ", " + nrDeAngajati + ", " + venitMediuLunar + ", '" + shortDescription + "')";

                Hashtable parameters = new Hashtable();
                parameters.Add("@consultDate", consultDate);

                object insertedID = mDataBridge.ExecuteScalarQuery(nonQuery, parameters); // PG compliant
                mLastError = mDataBridge.LastError;

                if (insertedID != null && !insertedID.ToString().Equals(string.Empty))
                {
                    int insertedClevID = (int)insertedID;
                    if (insertedClevID != 0)
                    {
                        clevID = insertedClevID;
                        result = true;
                    }
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool DeleteVizitConsult(int clientVizitID)
        {
            bool result = false;
            try
            {
                string nonQuery = @"Delete From ConsultEval WHERE clevID = " + clientVizitID;

                result = mDataBridge.ExecuteNonQuery(nonQuery); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool DeleteVizitConsultFotoFilesFromDB(int clevid)
        {
            bool result = false;
            try
            {
                string nonQuery = @"Delete From ConsultEvalPhoto WHERE clevID = " + clevid;

                result = mDataBridge.ExecuteNonQuery(nonQuery); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateVizitConsult(int consultID, DateTime consultDate, int clientID, int operatorID, int tipConsultEvalID, string descriereConsult, int businessStateID, int nrDeAngajati, int venitMediuLunar, string shortDescription)
        {
            bool result = false;
            try
            {
                string nonQuery = @"Update ConsultEval "
                    + " SET data = @data "
                    + ", clientID = " + clientID + " "
                    + ", OperatorID = " + operatorID + " "
                    + ", TipConsultEval = " + tipConsultEvalID + " "
                    + ", BusinessState = " + businessStateID + " "
                    + ", employeesCount = " + nrDeAngajati + " "
                    + ", AverageMonthlyIncome = " + venitMediuLunar + " "
                    + ", ConsultDescription = '" + descriereConsult + "' "
                    + ", shortDescription = '" + shortDescription + "' "
                    + " WHERE clevID = " + consultID;

                Hashtable parameters = new Hashtable();
                parameters.Add("@data", consultDate);

                result = mDataBridge.ExecuteNonQuery(nonQuery, parameters); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetVisitsConsultPhotoByClevId(int clevID)
        {
            DataTable resultTable = new DataTable();
            try
            {
                string commandText = @"SELECT * FROM ConsultEvalPhoto WHERE clevID = " + clevID;

                resultTable = mDataBridge.ExecuteQuery(commandText); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return resultTable;
        }

        public bool AddVizitFoto(int clevID, string PhotoLink, string SmallPhotoLink, string OriginalFileName, out int photoID)
        {
            bool result = false;
            photoID = 0;
            try
            {
                string nonQuery = @"INSERT INTO ConsultEvalPhoto (clevID, PhotoLink, SmallPhotoLink, OriginalFileName)"
                    + " OUTPUT INSERTED.PhotoID "
                     + " VALUES ( " + clevID + ", '" + PhotoLink + "','" + SmallPhotoLink + "', '" + OriginalFileName + "')";

                object insertedID = mDataBridge.ExecuteScalarQuery(nonQuery); // PG compliant
                mLastError = mDataBridge.LastError;

                if (insertedID != null && !insertedID.ToString().Equals(string.Empty))
                {
                    int insertedPhotoID = (int)insertedID;
                    if (insertedPhotoID != 0)
                    {
                        photoID = insertedPhotoID;
                        result = true;
                    }
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool DeleteVizitFoto(int photoID)
        {
            bool result = false;

            try
            {
                string nonQuery = @"DELETE FROM ConsultEvalPhoto WHERE PhotoID = " + photoID;

                result = mDataBridge.ExecuteNonQuery(nonQuery); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        #endregion Vizits Consulting

        #region Reports

        public DataTable LoansPartOfCreditLine_Report(DateTime startDate, DateTime endDate, int creditLineID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;
            
            try
            {
                string query = "  SELECT "
                        + "   (Select FirstName " + Plus + " coalesce(LastName, '') From Client WHERE Client.ClientID = Loans.ClientID) as \"Nume\" "
                        + " , (Select Name From Classifiers WHERE Code = (Select gender From Client WHERE Client.ClientID = Loans.ClientID)) as \"Gen\" "
                        + " , (Select DateOfBirth From Client WHERE Client.ClientID = Loans.ClientID) as \"Data Nasterii\" "
                        + " , (Select PersonalID From Client WHERE Client.ClientID = Loans.ClientID) as \"Cod Personal\" "

                        + " , (Select Name From Classifiers WHERE Code = (Select raddress_raion From Client WHERE Client.ClientID = Loans.ClientID)) as Raion "
                        + " , (Select raddress_localitatea From Client WHERE Client.ClientID = Loans.ClientID) as \"Localitatea\" "
                        + " , (Select raddress_stradaAdresa From Client WHERE Client.ClientID = Loans.ClientID) as \"Adresa\" "
                        + " , (Select raddress_urbanRural From Client WHERE Client.ClientID = Loans.ClientID) as \"Urban/Rural\" "
                        + " , (Select Name From Classifiers WHERE Code = (Select studyLevel From Client WHERE Client.ClientID = Loans.ClientID)) as \"Nivel Educatie\" "

                        + " , (Select " + mDataBridge.LimitStringMSSQL + " tipafacere From Business Where Business.ClientID = Loans.ClientID " + mDataBridge.LimitStringPGSQL + ") as \"Tip Afacere\" "
                        + " , (Select Name From Classifiers WHERE Code = (Select " + mDataBridge.LimitStringMSSQL + " sector_afacere From Business Where Business.ClientID = Loans.ClientID " + mDataBridge.LimitStringPGSQL + ")) as \"Sector Afacere\" "
                        + " , (Select " + mDataBridge.LimitStringMSSQL + " descriereActivitate From Business Where Business.ClientID = Loans.ClientID  " + mDataBridge.LimitStringPGSQL + ") as \"Descriere Afacere\" "
                        + " , (Select " + mDataBridge.LimitStringMSSQL + " bussinessStartDate From Business Where Business.ClientID = Loans.ClientID  " + mDataBridge.LimitStringPGSQL + ") as \"Data inceperii Afacere\" "
                        + " , (Select " + mDataBridge.LimitStringMSSQL + " aniExperientaInDomeniu From Business Where Business.ClientID = Loans.ClientID  " + mDataBridge.LimitStringPGSQL + ") as \"Ani exp. in domeniu\" "
                        + " , (Select " + mDataBridge.LimitStringMSSQL + " aniTotalExperientaAfaceri From Business Where Business.ClientID = Loans.ClientID  " + mDataBridge.LimitStringPGSQL + ") as \"Ani exp. in afaceri\" "

                        + " , creditAmountValue as \"Suma creditului\" "
                        + " , nrOfCreditMonthValue as \"Perioada de mprumut\" "
                        + " , percentValue as \"Dobinda\" "
                        + " , loanStartDate as \"Data aplicariii\" "
                        + " , '' as \"Total rambursat\" "
                        + " , '' as \"Total ramas de rambursat\" "

                        + " From Loans "
                        + " WHERE fundingLineID = " + creditLineID
                        + " AND loans.version = (Select MAX(LN.version) From Loans as LN Where Loans.loanID = LN.loanID) "
                    //+ " AND loanid in (SELECT LoanID From LoanOrders WHERE calculatedData >= @inputStartDate AND calculatedData <= @inputEndDate) ";
                        + " AND loanStartDate >= @inputStartDate "
                        + " AND loanStartDate <= @inputEndDate ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@inputStartDate", startDate);
                parameters.Add("@inputEndDate", endDate);

                result = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable ListOfPaymentsInSelectedPeriod_Report(DateTime startDate, DateTime endDate, int foundingLineID, bool filterByFoundingLine, int programID, bool filterByProgram, int loanStateID, bool filterByloanState)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                //                SELECT 
                //calculatedData
                //, (Select Client.FirstName + ' ' + Client.LastName FROM Client WHERE Client.clientID = (SELECT Loans.clientID FROM Loans WHERE Loans.loanID = LoanOrders.LoanID AND Loans.version = (SELECT MAX(version) FROM Loans WHERE Loans.loanID = LoanOrders.LoanID))) as "Client Description"
                //, percentValue as "Dobinda"
                //, mainRate as "Credit"
                //, percentValue + mainRate as "Total catre plata"

                //, (CASE WHEN  currencyChangeRate <> 1
                //    THEN percentValue_Rated
                //    ELSE NULL END ) as "Dobinda in MDL"

                //, (CASE WHEN  currencyChangeRate <> 1
                //    THEN mainRate_Rated 
                //    ELSE NULL END) as "Credit in MDL"

                //, (CASE WHEN  currencyChangeRate <> 1
                //    THEN percentValue_Rated + mainRate_Rated 
                //    ELSE NULL END ) as "Total catre plata in MDL"

                // FROM LoanOrders WHERE calculatedData >= '2013-04-01' AND calculatedData <= '2013-04-30'

                string query = "SELECT "
                    + "  (SELECT contractCode FROM Loans WHERE Loans.loanID = LoanOrders.LoanID and loans.version = (SELECT Max(Version) FROM Loans WHERE Loans.loanID = LoanOrders.LoanID)) as contractCode "
                    + " , calculatedData "
                    + " , (Select coalesce(Client.FirstName,'') " + Plus + " coalesce(Client.LastName, '') FROM Client WHERE Client.clientID = (SELECT Loans.clientID FROM Loans WHERE Loans.loanID = LoanOrders.LoanID AND Loans.version = (SELECT MAX(version) FROM Loans WHERE Loans.loanID = LoanOrders.LoanID))) as \"ClientDescription\"  "
                    + " , percentValue as \"percentValue\" "
                    + " , mainRate as \"mainRate\" "
                    + " , percentValue + mainRate as \"TotalAmount\" "

                    //+ " , percentValue_Rated as \"percentValue_Rated\" "
                    //+ " , mainRate_Rated as \"mainRate_Rated\" "
                    //+ " , percentValue_Rated + mainRate_Rated as \"TotalAmount_Rated\" "

                    + "  FROM LoanOrders WHERE calculatedData >=  @inputStartDate AND calculatedData <= @inputEndDate  "
                    + (filterByProgram ? " AND loanID in (SELECT Loans.loanID FROM Loans WHERE Loans.programID = " + programID + ") " : string.Empty)
                    //  + " AND loanID in (SELECT Loans.loanID FROM Loans WHERE Loans.currencyID " + (showContractsOnlyInLey ? "=" : "<>") + " 1) "
                    + (filterByFoundingLine ? " AND loanID in (SELECT Loans.loanID FROM Loans WHERE Loans.fundingLineID = " + foundingLineID + " ) " : string.Empty)
                    + (filterByloanState ? " AND loanID in (SELECT Loans.loanID FROM Loans WHERE Loans.programID = 321 AND Loans.loanState = " + loanStateID + " and loans.version = (SELECT Max(Version) FROM Loans WHERE Loans.loanID = LoanOrders.LoanID)) " : string.Empty)
                    + " ORDER BY calculatedData ASC, \"ClientDescription\" ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@inputStartDate", startDate);
                parameters.Add("@inputEndDate", endDate);

                result = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable ImprumuturiAcordateInSelectedPeriod_Report(DateTime startDate, DateTime endDate, int foundingLineID, bool filterByFoundingLine, int programID, bool filterByProgram)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = "SELECT "
                    + "  contractCode as contractCode "
                    + " , loanStartDate as loanStartDate "
                    + " , (Select coalesce(Client.FirstName,'') " + Plus + " coalesce(Client.LastName, '') FROM Client WHERE Client.clientID = Loans.clientID) as \"ClientDescription\"  "
                    + " , percentValue as \"percentValue\" "
                    + " , creditAmountValue as \"creditAmountValue\" "
                    + " , creditAmountValue * currencyChangeRate as creditAmount_Rated "
                    + "  FROM Loans "
                    + " WHERE loanStartDate >=  @inputStartDate AND loanStartDate <= @inputEndDate "
                    + " AND version = (SELECT MAX(LL.Version) From Loans as LL WHERE LL.loanID = Loans.loanID) "
                    // + " AND currencyID " + (showContractsOnlyInLey ? "=" : "<>") + " 1"
                    + (filterByProgram ? " AND programID = " + programID + " " : string.Empty)
                    + (filterByFoundingLine ? " AND fundingLineID = " + foundingLineID + "  " : string.Empty)
                    + " ORDER BY loanStartDate ASC, \"ClientDescription\" ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@inputStartDate", startDate);
                parameters.Add("@inputEndDate", endDate);

                result = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable ConsultEvals_Report(DateTime startDate, DateTime endDate, int operatorID, bool filterByOperatorID, int foundingLineID, bool filterByFoundingLine, int programID, bool filterByProgram, int tipConsult, bool filterByTipConsult, int businessState, bool filterByBussinessState)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = "SELECT "
                    + " (Select coalesce(Client.FirstName,'') " + Plus + " coalesce(Client.LastName, '') FROM Client WHERE Client.clientID = ConsultEval.clientID) as \"ClientDescription\"  "
                    + " , Data as ConsultEvalDate "
                    + " , (SELECT NAME FROM Classifiers WHERE Code = (SELECT sector_afacere FROM Business WHERE Business.ClientID = ConsultEval.clientID)) as sector_afacere_string "
                    + " , (SELECT tipafacere FROM Business WHERE Business.ClientID = ConsultEval.clientID) as tipafacere "
                    + " , (SELECT bussinessStartDate FROM Business WHERE Business.ClientID = ConsultEval.clientID) as bussinessStartDate "
                    + " , (SELECT NAME FROM Classifiers WHERE Code = BusinessState) as BusinessState_string "
                    + " , (SELECT nrAngajati FROM Business WHERE Business.ClientID = ConsultEval.clientID) as nrAngajati "
                    + " , (SELECT venit_lunar_afacere FROM Business WHERE Business.ClientID = ConsultEval.clientID) as venit_lunar_afacere "
                    + " , ShortDescription as ShortDescription "
                    + " , (SELECT COUNT(CE.ClevID) FROM ConsultEval as CE WHERE CE.Data >=  @inputStartDate AND CE.Data <= @inputEndDate AND CE.ClientID = ConsultEval.ClientID ) as nrOfVizits "

                    + "  FROM ConsultEval "
                    + " WHERE Data >=  @inputStartDate AND Data <= @inputEndDate "
                    + (filterByOperatorID ? " AND OperatorID = " + operatorID + " " : string.Empty)
                    + (filterByFoundingLine ? " AND ClientID in (SELECT LN.clientID FROM LOANS as LN WHERE LN.fundingLineID = " + foundingLineID + " ) " : string.Empty)
                    + (filterByProgram ? " AND ClientID in (SELECT LN.clientID FROM LOANS as LN WHERE LN.programID = " + programID + " ) " : string.Empty)
                    + (filterByTipConsult ? " AND TipConsultEval = " + tipConsult + " " : string.Empty)
                    + (filterByBussinessState ? " AND BusinessState = " + businessState + " " : string.Empty)

                    + " ORDER BY ConsultEvalDate, \"ClientDescription\" ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@inputStartDate", startDate);
                parameters.Add("@inputEndDate", endDate);

                result = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable ClientPersonalData_Report(int countryCode, int raionCode, string urmabRural, int genderCode, int churchCode, int sectorAfacereCode, int programID, int creditLineID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;
                       
            try
            {
                string query = "SELECT "
                        + "  ROW_NUMBER() OVER(Order By Client.FirstName) AS \"Nr\""
                        + " , coalesce(Client.FirstName,'') " + Plus + " coalesce(Client.LastName, '') as \"ClientDescription\"  "
                        + " , (Select name from Classifiers WHERE code = raddress_raion) as raddress_raion "
                        + " , raddress_localitatea "
                        + " , raddress_stradaAdresa "
                        + " , telefonFix "
                        + " , telefonMobil "
                        + " , (Select Name From Classifiers WHERE Code = church) as church_string "
                        + " , (Select " + mDataBridge.LimitStringMSSQL + " tipafacere From Business Where Business.ClientID = Client.ClientID  " + mDataBridge.LimitStringPGSQL + ") as \"Tip Afacere\" "
                        + " , (Select Name From Classifiers WHERE Code = (Select " + mDataBridge.LimitStringMSSQL + " sector_afacere From Business Where Business.ClientID = Client.ClientID  " + mDataBridge.LimitStringPGSQL + ")) as \"Sector Afacere\" "

                        + "  FROM Client WHERE "
                        + " raddress_country = " + countryCode
                        + (raionCode != 0 ? " AND raddress_raion = " + raionCode : string.Empty)
                        + (!urmabRural.Equals("**") && !urmabRural.Equals(string.Empty) ? " AND raddress_urbanRural = '" + urmabRural + "' " : string.Empty)
                        + (genderCode != 0 ? " AND gender = " + genderCode : string.Empty)
                        + (churchCode != 0 ? " AND church = " + churchCode : string.Empty)
                        + (sectorAfacereCode != 0 ? " AND clientID in (SELECT Business.ClientID FROM Business WHERE Business.sector_afacere = " + sectorAfacereCode + " ) " : string.Empty)
                        + (programID != 0 ? " AND clientID in (SELECT Loans.clientID FROM Loans WHERE Loans.programID = " + programID + " ) " : string.Empty)
                        + (creditLineID != 0 ? " AND clientID in (SELECT Loans.clientID FROM Loans WHERE Loans.fundingLineID = " + creditLineID + " ) " : string.Empty)

                        + " ORDER BY \"ClientDescription\" ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable PAR_Report(int managerID, DateTime toDate, int minDelayAmount)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                DateTime today_30 = toDate.AddDays(-30);
                DateTime today_60 = toDate.AddDays(-60);
                DateTime today_90 = toDate.AddDays(-90);
                DateTime today_180 = toDate.AddDays(-180);

                string query = "SELECT \r\n "
                    + " Loans.loanID  \r\n "
                    + " , " + minDelayAmount + " as MinAmount \r\n "
                    + " , Loans.contractCode  \r\n "
                    + " ,(Select coalesce(Client.FirstName,'') " + Plus + " coalesce(Client.LastName, '') FROM Client WHERE Client.clientID = Loans.clientID) as ClientDescription  \r\n "
                    + " , Loans.creditAmountValue  \r\n "
                    + " , coalesce(Loans.creditAmountValue, 0) - coalesce(Fin.mainRate, 0) as RemainingAmount  \r\n "


                    + " , CASE WHEN Del3.mainRate > 0 \r\n "
                    + " THEN Del3.calculatedData  \r\n "
                    + " ELSE "
                    + " 	CASE WHEN Del2.mainRate > 0   \r\n "
                    + " 	THEN Del2.calculatedData   \r\n "
                    + " 	ELSE "
                    + " 		CASE WHEN Del1.mainRate > 0  \r\n  "
                    + " 		THEN Del1.calculatedData   \r\n "
                    + " 		ELSE  \r\n "
                    + " 			CASE WHEN Del0.mainRate > 0   \r\n "
                    + " 			THEN Del0.calculatedData   \r\n "
                    + " 			END \r\n  "
                    + " 		END  \r\n "
                    + " 	END \r\n  "
                    + " END  as FirstDelayDay   \r\n "

                    + " , Del0.mainRate as Delay0  \r\n "
                    + " , Del1.mainRate as Delay30  \r\n "
                    + " , Del2.mainRate as Delay90  \r\n "
                    + " , Del3.mainRate as Delay180  \r\n "

                    + " FROM Loans   \r\n "
                    + "  LEFT JOIN ( SELECT loanid, SUM(mainRate)as mainRate, Min(calculatedData) as calculatedData	FROM LoanOrders WHERE  ( finishedTranche is null OR finishedTranche =  0 ) AND calculatedData >= @today_180 AND calculatedData <= @toDate group by loanID ) As Del0 ON Loans.loanID = Del0.LoanID \r\n "
                    + "  LEFT JOIN ( SELECT loanid, SUM(mainRate)as mainRate, Min(calculatedData) as calculatedData	FROM LoanOrders WHERE  ( finishedTranche is null OR finishedTranche =  0 ) AND calculatedData >= @today_180 AND calculatedData <= @today_30 group by loanID ) As Del1 ON Loans.loanID = Del1.LoanID \r\n  "
                    + "  LEFT JOIN ( SELECT loanid, SUM(mainRate)as mainRate, Min(calculatedData) as calculatedData	FROM LoanOrders WHERE  ( finishedTranche is null OR finishedTranche =  0 ) AND calculatedData >= @today_180 AND calculatedData <= @today_60 group by loanID ) As Del2 ON Loans.loanID = Del2.LoanID \r\n  "
                    + "  LEFT JOIN ( SELECT loanid, SUM(mainRate)as mainRate, Min(calculatedData) as calculatedData	FROM LoanOrders WHERE  ( finishedTranche is null OR finishedTranche =  0 ) AND calculatedData >= @today_180 AND calculatedData <= @today_90 group by loanID ) As Del3 ON Loans.loanID = Del3.LoanID \r\n  "
                    + "  LEFT JOIN ( SELECT loanid, SUM(mainRate)as mainRate FROM LoanOrders WHERE  finishedTranche =  1 group by loanID ) As Fin ON Loans.loanID = Fin.LoanID \r\n  "

                    + "  WHERE   \r\n "
                    + "   loans.version = (Select MAX(L.Version) FROM Loans as L WHERE L.loanID = Loans.loanID) \r\n  "
                    + " AND Del0.mainRate > " + minDelayAmount
                    + " AND Loans.loanManager = " + managerID
                    + " ORDER BY Loans.contractCode ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@notFins", false);
                parameters.Add("@Fins", true);
                parameters.Add("@toDate", toDate);
                parameters.Add("@today_180", today_180);
                parameters.Add("@today_90", today_90);
                parameters.Add("@today_60", today_60);
                parameters.Add("@today_30", today_30);

                result = mDataBridge.ExecuteQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public int GetManagersContract(int managerID)
        {
            int result = 0;

            try
            {
                string query = "SELECT Count(loanManager) FROM Loans \r\n "
                    + "  WHERE   \r\n "
                    + "   loans.version = (Select MAX(L.Version) FROM Loans as L WHERE L.loanID = Loans.loanID) \r\n  "
                    + " AND Loans.loanManager = " + managerID;

                DataTable tempResult = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (tempResult != null && tempResult.Rows.Count == 1 && tempResult.Rows[0][0] != System.DBNull.Value)
                {
                    result = (int)tempResult.Rows[0][0];
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        #endregion Reports

        #region PLEDGE

        public bool AddLoanPledgesDetails(int loanID, int pledgeID, int loanVersion, string denumirea, int pledgeType, int clientID, string address, string description, int pretpiata, int sumagajata)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " INSERT INTO LoanPledgesDetails (loanID, pledgeID, version, denumirea,     pledgeType,        proprietar,   [address], [description],    pretpiata,         sumagajata ) \r\n "
                        + " VALUES (" + loanID + ", " + pledgeID + ", " + loanVersion + " , @denumirea, " + pledgeType + ", " + clientID + ", @address, @description, " + pretpiata + ", " + sumagajata + "); ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@denumirea", denumirea);
                parameters.Add("@address", address);
                parameters.Add("@description", description);

                result = mDataBridge.ExecuteNonQuery(query, parameters); // PG compliant
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetPledgeListByLoanID(int loanID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT * "
                     + " , coalesce(c.FirstName, '') " + mDataBridge.ConcatSimbol + " ' ' " + mDataBridge.ConcatSimbol + " coalesce(c.LastName, '') " + mDataBridge.ConcatSimbol + " ' : ' " + mDataBridge.ConcatSimbol + " coalesce(CONVERT(VARCHAR(10), c.DateOfBirth, 104), '') " + mDataBridge.ConcatSimbol + " ' : ' " + mDataBridge.ConcatSimbol + " CL.Name COLLATE Romanian_CI_AS    as ShortDescription \r\n "
                     + " , (Select Name From Classifiers Where Code = PD.pledgeType ) as pledgeType_string "

                     + "  FROM LoanPledgesDetails as PD LEFT JOIN Client as C ON PD.proprietar = C.clientID LEFT JOIN Classifiers as CL ON PD.pledgeType = CL.Code \r\n "
                     + " WHERE PD.loanID = " + loanID + " \r\n "
                     + " AND PD.version = (SELECT MAX(version) FROM LoanPledgesDetails WHERE loanID = " + loanID + "); ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool AddLoanFidejusori(int loanID, int clientID, int orderID)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                int version = 0;
                string versQuery = "SELECT MAX(version) FROM Loans WHERE LoanID = " + loanID;
                DataTable versDT = mDataBridge.ExecuteQuery(versQuery);

                if (versDT != null && versDT.Rows.Count == 1 && versDT.Rows[0][0] != null)
                {
                    version = (int)versDT.Rows[0][0];
                }

                string query = "INSERT INTO LoanFidejusori (loanID, clientID, version, id) \r\n "
                + " VALUES (" + loanID + ", " + clientID + ", " + version + ", " + orderID + " );";

                result = mDataBridge.ExecuteNonQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        //public DataTable GetLoanFidejusoriListByLoanID(int loanID)
        //{
        //    DataTable result = new DataTable();
        //    mLastError = string.Empty;

        //    try
        //    {
        //        string query = " SELECT \r\n "
        //            + " F.loanID \r\n "
        //            + " ,F.clientID \r\n "
        //            + " , coalesce(c.FirstName, '') " + mDataBridge.ConcatSimbol + " ' ' " + mDataBridge.ConcatSimbol + " coalesce(c.LastName, '') " + mDataBridge.ConcatSimbol + " ' : ' " + mDataBridge.ConcatSimbol + " coalesce(CONVERT(VARCHAR(10), c.DateOfBirth, 104), '')   as ShortDescription \r\n "

        //            + "  FROM LoanFidejusori as F LEFT JOIN Client as C ON F.clientID = C.clientID  \r\n "
        //            + " WHERE F.loanID = "  + loanID + " \r\n "
        //            + " AND F.version = (SELECT MAX(version) FROM LoanFidejusori WHERE loanID = " + loanID + "); ";

        //        result = mDataBridge.ExecuteQuery(query);
        //        mLastError = mDataBridge.LastError;
        //    }
        //    catch (Exception exception)
        //    {
        //        mLastError += "Error using DataBridge. " + exception.Message;
        //    }

        //    return result;
        //}

        public List<Credits.DataObjects.Fidejusor> GetLoanFidejusoriObjectListByLoanID(int loanID)
        {
            List<Credits.DataObjects.Fidejusor> result = new List<DataObjects.Fidejusor>();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT \r\n "
                    + " F.loanID \r\n "
                    + " , F.clientID as FidejusorID \r\n "
                    + " , F.ID \r\n "
                    + " , coalesce(c.FirstName, '') " + mDataBridge.ConcatSimbol + " ' ' " + mDataBridge.ConcatSimbol + " coalesce(c.LastName, '') as FidejusortFirstLastName  \r\n"
                    + " , c.personalID as FidejusorIDNO  \r\n"
                    + " , c.DateOfBirth as FidejusorBirthDate  \r\n"
                    + " , (Select Name From Classifiers WHERE Code = c.viza_country) as \"Fidejusor Country\"  \r\n "
                    + " , (Select Name From Classifiers WHERE Code = c.viza_raion) as \"Fidejusor Raion\"   \r\n"
                    + " , c.viza_localitatea as \"Fidejusor Localitatea\"   \r\n"
                    + " , c.viza_stradaAdresa as \"FidejusorAdresa\"  \r\n "
                    + " , Loans.loanStartDate  \r\n"
                    + " , Loans.contractCode \r\n "
                    + " , Loans.ClientID as LoanClientID  \r\n"
                    + " , coalesce(CL2.FirstName, '')  " + mDataBridge.ConcatSimbol + "  ' '   " + mDataBridge.ConcatSimbol + "  coalesce(CL2.LastName, '') as ClientFirstLastName   \r\n "
                    + " , coalesce(CL2.personalID, '') as ClientIDNP   \r\n "
                    + " , Loans.creditAmountValue as LoanAmount  \r\n"
                    + " , Loans.loanEndDate  \r\n"

                    + "  FROM LoanFidejusori as F \r\n "
                    + "  LEFT JOIN Client as C ON F.clientID = C.clientID  \r\n "
                    + "  LEFT JOIN Loans ON F.LoanID = Loans.loanID AND   F.version = Loans.version\r\n "
                    + "  LEFT join Client as CL2 ON Loans.clientID = CL2.clientID  \r\n "
                    + "  WHERE F.loanID =  " + loanID + " \r\n "
                    + "  AND F.version = (SELECT MAX(version) FROM Loans WHERE loanID =  " + loanID + ") \r\n "
                    + "  ORDER BY F.ID \r\n ";

                DataTable tempResult = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (tempResult != null && tempResult.Rows.Count > 0)
                {
                    for (int i = 0; i < tempResult.Rows.Count; i++)
                    {
                        Credits.DataObjects.Fidejusor fidejusor = new DataObjects.Fidejusor(tempResult.Rows[i]);

                        if (fidejusor != null)
                            result.Add(fidejusor);
                    }
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public int GetNextAviableContractCode()
        {
            int result = 0;
            mLastError = string.Empty;

            try
            {
                string query = " SELECT MAX(contractCode) FROM Loans ";

                DataTable tempDT = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (tempDT != null && tempDT.Rows.Count == 1 && tempDT.Rows[0][0] != System.DBNull.Value)
                {
                    result = (int)tempDT.Rows[0][0];
                    result++;
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        #endregion PLEDGE

        #region Analiz Coments

        public DataTable GetGetLoanAnalizComments(int loanID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT \r\n "
                    + "  LAM.loanID, \r\n "
                    + "   LAM.Comment, \r\n "
                    + "   LAM.userID, \r\n "
                    + "   LAM.editdate, \r\n "
                    + "   coalesce(Users.Nume, '') " + mDataBridge.ConcatSimbol + " ' ' " + mDataBridge.ConcatSimbol + " coalesce(Users.Prenume, '') " + mDataBridge.ConcatSimbol + "'      '" + mDataBridge.ConcatSimbol + " convert(varchar, editdate, 120) as UserDate \r\n "
                    + " FROM  \r\n "
                    + "   LoanAnalizMessages as LAM LEFT JOIN Users ON Users.UserID = LAM.UserID \r\n "
                    + " WHERE LAM.loanID = " + loanID
                    + " ORDER BY editdate ASC  ;";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool AddAnalizComment(int loanID, string comment, int userID)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " INSERT INTO LoanAnalizMessages (loanID, Comment, userID, editdate) "
                                    + " VALUES (" + loanID + ", @commment, " + userID + ", @date)";

                Hashtable parameters = new Hashtable();
                parameters.Add("@date", DateTime.Now);
                parameters.Add("@commment", comment);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        #endregion Analiz Coments

        #region Intirzieri Coments

        public DataTable GetLoanDelayMessageGroups(int loanID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string groupsMessageSQL = "SELECT LoanID, MessageGroupID, GroupName, Creationdate, activeStatus FROM LoanDelaysMessageActiv WHERE LoanID = " + loanID;
                result = mDataBridge.ExecuteQuery(groupsMessageSQL);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetLoanDelaysComments(int loanID, int groupID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT "
                        + "     LoanID, "
                        + "     MessageGroupID, "
                        + "     MessageID, "
                        + "     Comment, "
                        + "     editdate, "
                        + "     coalesce(Users.Nume, '') " + mDataBridge.ConcatSimbol + " ' ' " + mDataBridge.ConcatSimbol + " coalesce(Users.Prenume, '') " + mDataBridge.ConcatSimbol + "'      '" + mDataBridge.ConcatSimbol + " convert(varchar, editdate, 120) as UserDate \r\n "
                        + "   FROM  "
                        + "     LoanDelaysMessages "
                        + " LEFT JOIN users ON LoanDelaysMessages.userID = users.userid "
                        + " WHERE loanID = " + loanID
                        + " AND MessageGroupID = " + groupID
                        + " ORDER BY editdate ASC  ;";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool AddDelaysGroup(int loanID, string GroupName)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " INSERT INTO LoanDelaysMessageActiv(LoanID, MessageGroupID, GroupName, Creationdate, activeStatus )  "
                            + " VALUES (" + loanID + ", (SELECT coalesce(MAX(MessageGroupID),0) + 1 FROM LoanDelaysMessageActiv WHERE LoanID = " + loanID + "),@GroupName, @Creationdate, @activeStatus); ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@Creationdate", DateTime.Now);
                parameters.Add("@activeStatus", true);
                parameters.Add("@GroupName", GroupName);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool CloseActiveDelaysGroup(int loanID, int groupID)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " UPDATE LoanDelaysMessageActiv SET activeStatus = @activeStatus  WHERE LoanID = " + loanID + " AND MessageGroupID = " + groupID;

                Hashtable parameters = new Hashtable();
                parameters.Add("@activeStatus", false);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }
        public bool AddDelaysComment(int loanID, int groupID, string comment, int userID)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " INSERT INTO LoanDelaysMessages (LoanID, MessageGroupID, MessageID, Comment, userID, editdate) "
                                + " VALUES (" + loanID + "," + groupID + ", (SELECT coalesce(MAX(MessageID),0) + 1 FROM LoanDelaysMessages WHERE LoanID = " + loanID + " AND MessageGroupID = " + groupID + ") , @Comment, " + userID + ", @editdate); ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@editdate", DateTime.Now);
                parameters.Add("@Comment", comment);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        #endregion Intirzieri Coments
        
        #region Loan Aproves

        public DataTable GetAprovesListStatusByLoanID(int loanID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT "
                      + "  LAS.loanID "
                      + "  , LAS.UserID "
                      + "  ,LAS.ApruveStatusID "
                      + "  ,LAS.Comment "
                      + "  , cast(LAS.UserID as varchar) " + mDataBridge.ConcatSimbol + " '&' " + mDataBridge.ConcatSimbol + " cast(LAS.MessageID as varchar) " + mDataBridge.ConcatSimbol + " '&' " + mDataBridge.ConcatSimbol + " cast(LAS.ApruveStatusID as varchar)  as RowKey"
                      + " , CL.Name as ApruveStatusID_string "
                      + " , coalesce(Users.Nume, '') " + mDataBridge.ConcatSimbol + " ' ' " + mDataBridge.ConcatSimbol + " coalesce(Users.Prenume, '') " + mDataBridge.ConcatSimbol + "'      '" + mDataBridge.ConcatSimbol + " convert(varchar, Date, 120) as UserDate \r\n "
                      + " , coalesce(Users.Nume, '') " + mDataBridge.ConcatSimbol + " ' ' " + mDataBridge.ConcatSimbol + " coalesce(Users.Prenume, '') " + mDataBridge.ConcatSimbol + "'  ->  '" + mDataBridge.ConcatSimbol + " CL.Name as UserApruveStatus \r\n "
                      + "  FROM LoanApruveStatus as LAS LEFT JOIN Users ON Users.UserID = LAS.UserID LEFT JOIN Classifiers as CL ON LAS.ApruveStatusID = CL.Code  \r\n "
                      + " WHERE LAS.loanID = " + loanID + " \r\n "
                      + " AND LAS.version = (SELECT Max(Version) From LoanApruveStatus WHERE loanID = LAS.LoanID AND userID = LAS.UserID AND MessageID = LAS.MessageID) "
                      + " ORDER BY Date ASC ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public DataTable GetAprovesListStatusByLoanIDForShortDisplay(int loanID)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT "
                      + "  LAS.loanID "
                      + "  , LAS.UserID "
                      + "  ,LAS.ApruveStatusID "
                      + "  ,LAS.Comment "
                      + "  , cast(LAS.UserID as varchar) " + mDataBridge.ConcatSimbol + " '&' " + mDataBridge.ConcatSimbol + " cast(LAS.MessageID as varchar) " + mDataBridge.ConcatSimbol + " '&' " + mDataBridge.ConcatSimbol + " cast(LAS.ApruveStatusID as varchar)  as RowKey"
                      + " , CL.Name as ApruveStatusID_string "
                      + " , coalesce(Users.Nume, '') " + mDataBridge.ConcatSimbol + " ' ' " + mDataBridge.ConcatSimbol + " coalesce(Users.Prenume, '') " + mDataBridge.ConcatSimbol + "'      '" + mDataBridge.ConcatSimbol + " convert(varchar, Date, 120) as UserDate \r\n "
                      + " , coalesce(Users.Nume, '') " + mDataBridge.ConcatSimbol + " ' ' " + mDataBridge.ConcatSimbol + " coalesce(Users.Prenume, '') " + mDataBridge.ConcatSimbol + "'  ->  '" + mDataBridge.ConcatSimbol + " CL.Name as UserApruveStatus \r\n "
                      + "  FROM LoanApruveStatus as LAS LEFT JOIN Users ON Users.UserID = LAS.UserID LEFT JOIN Classifiers as CL ON LAS.ApruveStatusID = CL.Code  \r\n "
                      + " WHERE LAS.loanID = " + loanID + " \r\n "
                      + " AND LAS.version = (SELECT Max(Version) From LoanApruveStatus WHERE loanID = LAS.LoanID AND userID = LAS.UserID AND messageID = LAS.messageID) "
                      + " AND LAS.messageID = (SELECT Max(MessageID) From LoanApruveStatus WHERE loanID = LAS.LoanID AND userID = LAS.UserID) "
                      + " ORDER BY Date ASC ";

                result = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public string GetCommentByParameters(int loanID, int userID, int messageID)
        {
            string result = string.Empty;
            mLastError = string.Empty;

            try
            {
                string query = " SELECT Comment FROM LoanApruveStatus AS LAS WHERE LAS.loanID = " + loanID + " AND UserID = " + userID + " AND MessageID= " + messageID
                     + " AND LAS.version = (SELECT MAX(version) FROM LoanApruveStatus  WHERE loanID = " + loanID + " AND UserID = " + userID + " AND MessageID= " + messageID + " ); ";

                DataTable dtresult = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (dtresult != null && dtresult.Rows.Count == 1)
                {
                    result = dtresult.Rows[0]["Comment"].ToString();
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool AddCommentByParameters(int loanID, int userID, DateTime date, int ApruveStatusID, string comment)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " INSERT INTO LoanApruveStatus (  LoanID,  UserID,  MessageID, version,  [Date],  ApruveStatusID,  Comment) "
                                    + " VALUES ( " + loanID + ", " + userID + ", coalesce((Select Max(MessageID) FROM LoanApruveStatus WHERE loanID = " + loanID + " AND UserID = " + userID + " ), 0) + 1, 1 , @date, " + ApruveStatusID + ",  @commment )";

                Hashtable parameters = new Hashtable();
                parameters.Add("@date", date);
                parameters.Add("@commment", comment);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateCommentByParameters(int loanID, int userID, int messageID, string comment)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " INSERT INTO LoanApruveStatus (  LoanID,  UserID,  MessageID,                                                           version,  [Date],  ApruveStatusID,  Comment) \r\n "
                                    + " SELECT LoanID, userID , MessageID, (SELECT MAX(version) FROM LoanApruveStatus as LAS1  WHERE LAS1.loanID = " + loanID + " AND LAS1.UserID = " + userID + " AND LAS1.messageID=" + messageID + " ) + 1  ,[Date] , ApruveStatusID,  @commment  FROM LoanApruveStatus \r\n "
                                    + " WHERE loanID = " + loanID + " AND UserID = " + userID + " AND messageID = " + messageID + " AND Version = (SELECT MAX(version) FROM LoanApruveStatus as LAS  WHERE LAS.loanID = " + loanID + " AND LAS.UserID = " + userID + " AND LAS.messageID=" + messageID + " ) \r\n  ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@commment", comment);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool TestForAllAproves(Credits.DataObjects.LoanObject loanObject)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = " SELECT * "
                     + "  FROM LoanApruveStatus as LAS LEFT JOIN Users ON Users.UserID = LAS.UserID LEFT JOIN Classifiers as CL ON LAS.ApruveStatusID = CL.Code  \r\n "
                     + " WHERE LAS.loanID = " + loanObject.LoanID + " \r\n "
                     + " AND LAS.ApruveStatusID <> " + (int)Constants.Constants.Classifiers.AproveStatus_Aprove + " "
                     + " AND LAS.version = (SELECT MAX(version) FROM LoanApruveStatus  WHERE userid = LAS.UserID AND loanID = " + loanObject.LoanID + "); ";

                DataTable dtresult = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (dtresult != null && dtresult.Rows.Count == 0)
                {
                    loanObject.LoanState = (int)Constants.Constants.Classifiers.CreditState_SpreEliberareNumerer;
                    result = UpdateLoan(loanObject);
                }
                else
                {
                    string refQuery = " SELECT * "
                                    + "  FROM LoanApruveStatus as LAS LEFT JOIN Users ON Users.UserID = LAS.UserID LEFT JOIN Classifiers as CL ON LAS.ApruveStatusID = CL.Code  \r\n "
                                    + " WHERE LAS.loanID = " + loanObject.LoanID + " \r\n "
                                    + " AND LAS.ApruveStatusID in (" + (int)Constants.Constants.Classifiers.CreditState_Refuzat + ", " + (int)Constants.Constants.Classifiers.AproveStatus_NeedAdditinalInfo + ")"
                                    + " AND LAS.version = (SELECT MAX(version) FROM LoanApruveStatus  WHERE userid = LAS.UserID AND loanID = " + loanObject.LoanID + "); ";

                    DataTable refDTResult = mDataBridge.ExecuteQuery(refQuery);
                    mLastError = mDataBridge.LastError;

                    if (refDTResult != null && refDTResult.Rows.Count > 0)
                    {
                        loanObject.LoanState = (int)Constants.Constants.Classifiers.CreditState_Refuzat;
                        result = UpdateLoan(loanObject);
                    }
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public int GetAproveStatusByLoanIDAndUserID(int loanID, int userID)
        {
            int result = 0;
            mLastError = string.Empty;

            try
            {
                string query = " SELECT * "
                     + "  FROM LoanApruveStatus \r\n "
                     + " WHERE loanID = " + loanID + " \r\n "
                     + " AND UserID = " + userID + " \r\n "
                     + " AND version = (SELECT MAX(version) FROM LoanApruveStatus WHERE loanID = " + loanID + "); ";

                DataTable TempResult = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;
                if (TempResult != null && TempResult.Rows.Count == 1 && TempResult.Rows[0]["ApruveStatusID"] != System.DBNull.Value)
                {
                    result = (int)TempResult.Rows[0]["ApruveStatusID"];
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateLoanAproveStatus(int loanID, int userID, int apruveStatus, DateTime date, string comment)
        {
            bool result = false;
            mLastError = string.Empty;
            int version = 0;

            try
            {
                string query = " SELECT version  FROM LoanApruveStatus as APS \r\n "
                    + " WHERE APS.loanID = " + loanID + " \r\n "
                    + " AND APS.UserID = " + userID + " \r\n "
                    + " AND APS.version = (SELECT MAX(version) FROM LoanApruveStatus WHERE UserID = APS.UserID AND loanID = " + loanID + ")";

                object versionObject = mDataBridge.ExecuteScalarQuery(query);
                if (versionObject != null)
                {
                    version = (int)versionObject;
                }

                version++;

                query = " INSERT INTO LoanApruveStatus (LoanID, UserID, version, [Date], ApruveStatusID, Comment) \r\n "
                             + " VALUES ( " + loanID + "," + userID + ", " + version + ", @Date," + apruveStatus + ", @comment ); ";

                Hashtable parameters = new Hashtable();
                parameters.Add("@Date", date);
                parameters.Add("@comment", comment);

                result = mDataBridge.ExecuteNonQuery(query, parameters);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateComitetMembers(int loanID, List<int> userList)
        {
            bool result = false;
            mLastError = string.Empty;

            string usersListString = Crypt.Utils.ConvertListToString(userList);

            try
            {
                string deletequery = "DELETE FROM LoanApruveStatus WHERE LoanID = " + loanID + " AND userID not in (" + usersListString + ")";
                result = mDataBridge.ExecuteNonQuery(deletequery);
                mLastError = mDataBridge.LastError;

                for (int i = 0; i < userList.Count; i++)
                {
                    int UserID = userList[i];

                    string query = " INSERT INTO LoanComitetMember ( LoanID, UserID) VALUES ( " + loanID + ", " + UserID + "); ";
                    result = mDataBridge.ExecuteNonQuery(query);
                    mLastError = mDataBridge.LastError;
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public List<string> GetRegisteredComitetMemberList(int loanID)
        {
            List<string> resultList = new List<string>();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT UserID "
                      + "  FROM LoanComitetMember  \r\n "
                      + " WHERE loanID = " + loanID + " \r\n ";

                DataTable temDT = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (temDT != null && temDT.Rows.Count > 0)
                {
                    for (int i = 0; i < temDT.Rows.Count; i++)
                    {
                        string userID = temDT.Rows[i][0].ToString();
                        resultList.Add(userID);
                    }
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return resultList;
        }

        #endregion Loan Aproves

        #region currency history
        public void CheckChangeRateForAllDays()
        {
            DateTime startDate = new DateTime(2010, 1, 1);
            DateTime finalDay = DateTime.Now.Date;

            int TotalDaysPassed = (finalDay - startDate).Days + 1;

            try
            {
                string totalDays = string.Empty;
                for (int i = 0; i < TotalDaysPassed; i++)
                {
                    if (i > 0) totalDays += ",";
                    totalDays += "@date" + i;
                }

                string query = "   WITH Alldates AS( \r\n ";

                for (int i = 0; i < TotalDaysPassed; i++)
                {
                    if (i > 0) query += " UNION ALL \r\n ";
                    query += " SELECT @date" + i + " as day , " + (int)Constants.Constants.CurrencyList.EURO + " as curr \r\n ";
                    query += " UNION ALL \r\n ";
                    query += " SELECT @date" + i + " as day , " + (int)Constants.Constants.CurrencyList.USD + " as curr \r\n ";
                }
                query += ") \r\n ";

                query += " SELECT day, curr FROM \r\n "
                            + " Alldates \r\n "
                            + " LEFT JOIN RateHistroy ON  \r\n "
                            + " Alldates.day = RateHistroy.[data]  \r\n "
                            + " AND Alldates.curr = RateHistroy.currency  \r\n "
                            + " WHERE  Alldates.Day not in (SELECT [data] FROM RateHistroy WHERE rate > 0 AND Alldates.curr = RateHistroy.currency  )  \r\n ";

                Hashtable parametres = new Hashtable();
                for (int i = 0; i < TotalDaysPassed; i++)
                {
                    parametres.Add("@date" + i, startDate.AddDays(i));
                }

                DataTable tempDT = mDataBridge.ExecuteQuery(query, parametres);
                mLastError = mDataBridge.LastError;

                if (tempDT != null && tempDT.Rows.Count > 0)
                {
                    for (int i = 0; i < tempDT.Rows.Count; i++)
                    {
                        if (tempDT.Rows[i]["day"] != System.DBNull.Value)
                        {
                            DateTime day = (DateTime)tempDT.Rows[i]["day"];

                            WebClient client = new WebClient();
                            XmlDocument xmlDoc = new XmlDocument();

                            string xmlStringDocumnet = string.Empty;
                            string response = client.DownloadString("http://bnm.md/md/official_exchange_rates?get_xml=1&date=" + day.ToString(Constants.Constants.ISODateBackwardDotsFormat));
                            xmlDoc.LoadXml(response);

                            try
                            {
                                if (GetCurrencyRateByDate(day, (int)Constants.Constants.CurrencyList.EURO) == 0)
                                {
                                    XmlNode euroNode = xmlDoc.SelectSingleNode(@"/ValCurs/Valute[CharCode='EUR']");
                                    string euroChangeRateSTR = euroNode["Value"].InnerText;
                                    decimal euroChangeRate = Crypt.Utils.MyDecimalParce(euroChangeRateSTR);

                                    UpdateCurrencyRate(day, (int)Constants.Constants.CurrencyList.EURO, euroChangeRate);
                                }

                                if (GetCurrencyRateByDate(day, (int)Constants.Constants.CurrencyList.USD) == 0)
                                {
                                    XmlNode usdNode = xmlDoc.SelectSingleNode(@"/ValCurs/Valute[CharCode='USD']");
                                    string usdChangeRateSTR = usdNode["Value"].InnerText;
                                    decimal usdChangeRate = Crypt.Utils.MyDecimalParce(usdChangeRateSTR);

                                    UpdateCurrencyRate(day, (int)Constants.Constants.CurrencyList.USD, usdChangeRate);
                                }
                            }
                            catch
                            {
                            }
                        }
                    }
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }
        }

        public DataTable GetCurrencyRatesListByDate(DateTime date)
        {
            DataTable result = new DataTable();
            mLastError = string.Empty;

            try
            {
                string query = " SELECT [data], currency, rate FROM RateHistroy WHERE [data] = @date ";

                Hashtable parametres = new Hashtable();
                parametres.Add("@date", date);

                result = mDataBridge.ExecuteQuery(query, parametres);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public decimal GetCurrencyRateByDate(DateTime date, int currencyCode)
        {
            decimal result = 0;
            mLastError = string.Empty;

            try
            {
                string query = "SELECT rate FROM RateHistroy WHERE [data] = @date AND currency = " + currencyCode;

                Hashtable parametres = new Hashtable();
                parametres.Add("@date", date);

                DataTable tempResult = mDataBridge.ExecuteQuery(query, parametres);
                mLastError = mDataBridge.LastError;

                if (tempResult != null && tempResult.Rows.Count == 1 && tempResult.Rows[0][0] != System.DBNull.Value)
                {
                    result = (decimal)tempResult.Rows[0][0];
                }
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        public bool UpdateCurrencyRate(DateTime date, int currencyCode, decimal changeRate)
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                string query = "SELECT rate FROM RateHistroy WHERE [data] = @date AND currency = " + currencyCode;

                Hashtable parametres = new Hashtable();
                parametres.Add("@date", date);

                DataTable tempResult = mDataBridge.ExecuteQuery(query, parametres);
                mLastError = mDataBridge.LastError;

                if (tempResult != null && tempResult.Rows.Count == 1)
                {
                    query = "UPDATE RateHistroy SET "
                        + " rate = @rate "
                        + " WHERE [data] = @date "
                        + " AND currency = @currency ";
                }
                else
                {
                    query = "INSERT INTO RateHistroy ([data], currency, rate) "
                        + " VALUES (@date, @currency,@rate)";
                }


                Hashtable parameters2 = new Hashtable();
                parameters2.Add("@rate", changeRate);
                parameters2.Add("@date", date);
                parameters2.Add("@currency", currencyCode);

                result = mDataBridge.ExecuteNonQuery(query, parameters2);
                mLastError = mDataBridge.LastError;
            }
            catch (Exception exception)
            {
                mLastError += "Error using DataBridge. " + exception.Message;
            }

            return result;
        }

        #endregion currency history

        #region Public funtions for calculate loans

        //double CreditAmount = 64776;
        //double percent = 0.11;
        //double nrOfMonths = 24;
        //double d = percent / 12;

        ///// Rate, NPer, PV
        //double pmt = Financial.Pmt(d, nrOfMonths, -CreditAmount);
        //double ppmt = Financial.PPmt(d, 1, nrOfMonths, -CreditAmount);  /// ratapentru luna 1
        //double fv = Financial.FV(d, nrOfMonths, pmt);
        //double ipmt = Financial.IPmt(d, 1, nrOfMonths, -CreditAmount);  //// procet din suma ramasa 
        //double nper = Financial.NPer(d, pmt, -CreditAmount);
        //double pv = Financial.PV(d, nrOfMonths, pmt);
        //double rate = Financial.Rate(nrOfMonths, pmt, pv); 


        public static int GetNrPlatilorPeAn(int payPeriodCode)
        {
            int result = 0;

            switch (payPeriodCode)
            {
                case (int)Constants.Constants.Classifiers.OdataLaDouaSaptamini:
                    result = 24;
                    break;

                case (int)Constants.Constants.Classifiers.MonthlyPayment:
                    result = 12;
                    break;

                case (int)Constants.Constants.Classifiers.OdataLaDouaLuni:
                    result = 6;
                    break;

                case (int)Constants.Constants.Classifiers.QuarterlyPaymnet:
                    result = 4;
                    break;

                case (int)Constants.Constants.Classifiers.Half_yearPaymnet:
                    result = 2;
                    break;

                case (int)Constants.Constants.Classifiers.AnnuallyPayment:
                    result = 1;
                    break;

                default:
                    result = 12;
                    break;
            }

            return result;
        }

        public static DataTable PrepareOrdersTable(DateTime loanStartDate, decimal creditAmount, decimal creditPercent, int nrTotalDeTranse, int payPeriodCode, int calculatePernectFromCode, decimal currencyChangeRate, out DateTime loanEndDate)
        {
            #region Input Variable

            loanEndDate = DateTime.MinValue;

            DataTable resultTable = new DataTable();
            resultTable.Columns.Add("iterationID", typeof(int));
            resultTable.Columns.Add("calculatedData", typeof(DateTime));
            resultTable.Columns.Add("days", typeof(int));
            resultTable.Columns.Add("percentValue", typeof(decimal));
            resultTable.Columns.Add("mainRate", typeof(decimal));
            resultTable.Columns.Add("totalAmount", typeof(decimal));
            resultTable.Columns.Add("remainingAmount", typeof(decimal));
            resultTable.Columns.Add("currencyChangeRate", typeof(decimal));
            resultTable.Columns.Add("totalAmount_Rated", typeof(decimal));

            int realPayCount = 0;

            #endregion Input Variable

            decimal cumulativeRateAmount = 0;
            decimal remainingAmount = 0;
            decimal totalAmountForPay = 0;
            decimal rate = creditPercent / 100 / GetNrPlatilorPeAn(payPeriodCode);
            decimal nrRealDeTranse = GetTotalNumberOfPaymnets(nrTotalDeTranse, payPeriodCode);

            for (int i = 0; i < nrRealDeTranse; i++)
            {
                resultTable.Rows.Add();
                realPayCount++;

                DateTime previousCalculatedDate = DateTime.MinValue;
                if (resultTable.Rows.Count > 1)
                {
                    previousCalculatedDate = (DateTime)resultTable.Rows[resultTable.Rows.Count - 2]["calculatedData"];
                }
                else
                {
                    previousCalculatedDate = loanStartDate;
                }

                int realDaysPassed = 0;
                DateTime datePayment = Credits.Module.CalculateDataForPay(payPeriodCode, realPayCount, loanStartDate, previousCalculatedDate, out realDaysPassed);

                decimal rateAmountForPay = 0;
                decimal percentAmountForPay = 0;

                switch (calculatePernectFromCode)
                {
                    case (int)Constants.Constants.Classifiers.Percent_DinSumaInitiala:
                        rateAmountForPay = (decimal)Math.Round((creditAmount / nrRealDeTranse), 2);
                        percentAmountForPay = (decimal)Math.Round((Microsoft.VisualBasic.Financial.IPmt((double)rate, 1, (double)nrRealDeTranse, -(double)creditAmount, 1, 0)), 2);
                        break;

                    case (int)Constants.Constants.Classifiers.Percent_DinSumaRamasa:
                        rateAmountForPay = (decimal)Math.Round((Microsoft.VisualBasic.Financial.PPmt((double)rate, i + 1, (double)nrRealDeTranse, -(double)creditAmount, 1, 0)), 2);
                        percentAmountForPay = (decimal)Math.Round((Microsoft.VisualBasic.Financial.IPmt((double)rate, i + 1, (double)nrRealDeTranse, -(double)creditAmount, 1, 0)), 2);
                        break;
                }

                if (i > 0) cumulativeRateAmount += (decimal)resultTable.Rows[i - 1]["mainRate"];
                remainingAmount = creditAmount - cumulativeRateAmount;

                if (i == nrRealDeTranse - 1)
                    rateAmountForPay = remainingAmount;

                totalAmountForPay = rateAmountForPay + percentAmountForPay;   

                #region  Atribuirea tuturor parametrilor

                resultTable.Rows[resultTable.Rows.Count - 1]["iterationID"] = realPayCount;
                resultTable.Rows[resultTable.Rows.Count - 1]["calculatedData"] = datePayment;
                resultTable.Rows[resultTable.Rows.Count - 1]["days"] = realDaysPassed;
                resultTable.Rows[resultTable.Rows.Count - 1]["percentValue"] = percentAmountForPay;
                resultTable.Rows[resultTable.Rows.Count - 1]["mainRate"] = rateAmountForPay;
                resultTable.Rows[resultTable.Rows.Count - 1]["totalAmount"] = totalAmountForPay;
                resultTable.Rows[resultTable.Rows.Count - 1]["remainingAmount"] = remainingAmount;
                resultTable.Rows[resultTable.Rows.Count - 1]["currencyChangeRate"] = currencyChangeRate;
                if (currencyChangeRate != 0)
                    resultTable.Rows[resultTable.Rows.Count - 1]["totalAmount_Rated"] = Math.Round((totalAmountForPay / currencyChangeRate), 2);

                loanEndDate = datePayment;
                #endregion Atribuirea tuturor parametrilor
            }

            return resultTable;
        }

        public static DateTime CalculateDataForPay(int payPeriod, int iterationNr, DateTime firstPayDate, DateTime previousPayDate, out int nrOfDays)
        {
            DateTime datePayment = DateTime.MinValue;
            nrOfDays = 0;

            //////  data calculation
            switch (payPeriod)
            {
                case (int)Constants.Constants.Classifiers.OdataLaDouaSaptamini:
                    datePayment = firstPayDate.AddDays(iterationNr * 14);
                    break;

                case (int)Constants.Constants.Classifiers.MonthlyPayment:  /// lunar
                    datePayment = firstPayDate.AddMonths(iterationNr);

                    if (datePayment.DayOfWeek == DayOfWeek.Saturday || datePayment.DayOfWeek == DayOfWeek.Sunday)
                    {
                        if (datePayment.DayOfWeek == DayOfWeek.Saturday) { datePayment = datePayment.AddDays(2); }
                        if (datePayment.DayOfWeek == DayOfWeek.Sunday) { datePayment = datePayment.AddDays(1); }
                    }
                    break;

                case (int)Constants.Constants.Classifiers.OdataLaDouaLuni:
                    datePayment = firstPayDate.AddMonths(iterationNr * 2);
                    break;

                case (int)Constants.Constants.Classifiers.QuarterlyPaymnet: /// simestrial
                    datePayment = firstPayDate.AddMonths(iterationNr * 3);
                    break;

                case (int)Constants.Constants.Classifiers.Half_yearPaymnet: /// jumate de an
                    datePayment = firstPayDate.AddMonths(iterationNr * 6);
                    break;

                case (int)Constants.Constants.Classifiers.AnnuallyPayment: /// anual
                    datePayment = firstPayDate.AddYears(iterationNr);
                    break;

                default:
                    break;
            }

            nrOfDays = (datePayment - previousPayDate).Days;

            return datePayment;
        }

        public static int GetTotalNumberOfPaymnets(int nrTotalLuniACreditului, int payPeriodCode)
        {
            int result = 0;

            switch (payPeriodCode)
            {
                case (int)Constants.Constants.Classifiers.OdataLaDouaSaptamini:
                    result = nrTotalLuniACreditului * 2;
                    break;

                case (int)Constants.Constants.Classifiers.MonthlyPayment:
                    result = nrTotalLuniACreditului;
                    break;

                case (int)Constants.Constants.Classifiers.OdataLaDouaLuni:
                    result = nrTotalLuniACreditului / 2;
                    if ((nrTotalLuniACreditului % 2) > 0)
                        result += 1;
                    break;

                case (int)Constants.Constants.Classifiers.QuarterlyPaymnet:
                    result = nrTotalLuniACreditului / 3;
                    if ((nrTotalLuniACreditului % 3) > 0)
                        result += 1;
                    break;

                case (int)Constants.Constants.Classifiers.Half_yearPaymnet:
                    result = nrTotalLuniACreditului / 6;
                    if ((nrTotalLuniACreditului % 6) > 0)
                        result += 1;
                    break;

                case (int)Constants.Constants.Classifiers.AnnuallyPayment:
                    result = nrTotalLuniACreditului / 12;
                    if ((nrTotalLuniACreditului % 12) > 0)
                        result += 1;
                    break;

                default:
                    break;
            }

            return result;
        }

        #endregion Public funtions for calculate loans

        public bool UpdateFinishedTranche(int loanValue, int iterationID)
        {
            bool result = false;

            try
            {
                DataTable refundTable = new DataTable();
                //refundTable = GetRefundsLoanOrderList(loanValue);

                refundTable = GetRefundsLoanOrderDT1(loanValue);

                bool isFinished = false;

                if (refundTable != null && refundTable.Rows.Count > 0)
                {
                    refundTable.Rows.RemoveAt(0);
                    refundTable.Rows.RemoveAt(refundTable.Rows.Count - 1);
                    int mIterationID = iterationID + 1;
                    DataRow[] mSelectedRows = refundTable.Select("iterationID = " + mIterationID);

                    decimal outstanding = -1;
                    decimal.TryParse(mSelectedRows[0]["outstandingID"].ToString(), out outstanding);
                    if (outstanding == 0)
                    {
                        isFinished = true;
                    }

                    //decimal sumLeft = 0;
                    //decimal sumRight = 0;

                    //for (int i = 0; i < mSelectedRows.Length; i++)
                    //{
                    //    decimal mainBasic = 0;
                    //    decimal profitBasic = 0;
                    //    decimal.TryParse(mSelectedRows[i][3].ToString(), out mainBasic);
                    //    decimal.TryParse(mSelectedRows[i][4].ToString(), out profitBasic);

                    //    string pays = mSelectedRows[i][8].ToString();
                    //    int paysID = 0;
                    //    int.TryParse(pays, out paysID);
                    //    if (paysID == 0)
                    //    {
                    //        sumLeft += mainBasic;
                    //        sumLeft += profitBasic;
                    //    }
                    //    else
                    //    {
                    //        sumRight += mainBasic;
                    //        sumRight += profitBasic;
                    //    }
                    //}

                    //if (sumLeft <= sumRight)
                    //{
                    //    isFinished = true;
                    //}

                    string query = " UPDATE LoanOrders SET "
                           + " finishedTranche = @finishedTranche "
                           + " WHERE LoanID = " + loanValue
                           + " AND iterationID = " + iterationID
                           + " ";

                    Hashtable parameters = new Hashtable();
                    parameters.Add("@finishedTranche", isFinished);

                    result = mDataBridge.ExecuteNonQuery(query, parameters);
                    mLastError = mDataBridge.LastError;
                }
            }
            catch (Exception ex)
            {
                mLastError += "Error using DataBridge. " + ex.Message;
            }

            return result;
        }

        public bool UpdateAllFinishedTranche()
        {
            bool result = false;
            mLastError = string.Empty;

            try
            {
                int loanValue = 0;

                string selectQuery = "SELECT DISTINCT LoanID "
                    + " FROM LoanOrders ";

                DataTable loansTable = new DataTable();
                loansTable = mDataBridge.ExecuteQuery(selectQuery);
                mLastError = mDataBridge.LastError;

                if (loansTable != null)
                {

                    for (int indexLoan = 0; indexLoan < loansTable.Rows.Count; indexLoan++)
                    {
                        int.TryParse(loansTable.Rows[indexLoan][0].ToString(), out loanValue);

                        result = UpdateFinishedTrancheByLoanID(loanValue);
                    }
                }
            }
            catch (Exception ex)
            {
                mLastError += "Error using DataBridge. " + ex.Message;
            }

            return result;
        }

        public bool UpdateFinishedTrancheByLoanID(int loanID)
        {
            bool result = false;

            int iterationID = 0;
            try
            {
                string selectQueryIterations = "SELECT iterationID "
                            + " FROM LoanOrders "
                            + " WHERE LoanID = " + loanID
                            + " ";

                DataTable iterationsTable = new DataTable();
                iterationsTable = mDataBridge.ExecuteQuery(selectQueryIterations);
                mLastError = mDataBridge.LastError;

                for (int indexIteration = 0; indexIteration < iterationsTable.Rows.Count; indexIteration++)
                {
                    int.TryParse(iterationsTable.Rows[indexIteration][0].ToString(), out iterationID);
                    result = UpdateFinishedTranche(loanID, iterationID);
                }
            }
            catch (Exception ex)
            {
                mLastError += "Error into GetIterationByLoanID function. " + ex.Message;
            }

            return result;
        }

        public int GetIterationByLoanID(int mLoanID)
        {
            int result = 0;
            try
            {
                string query = " SELECT min(iterationID) "
                    + " FROM LoanOrders "
                    + " WHERE LoanID = " + mLoanID
                    + " AND finishedTranche = 0 "
                    + " ";

                DataTable tempResult = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (tempResult != null && tempResult.Rows.Count == 1 && tempResult.Rows[0][0] != System.DBNull.Value)
                {
                    result = (int)tempResult.Rows[0][0];
                }
            }
            catch (Exception ex)
            {
                mLastError += "Error into GetIterationByLoanID function. " + ex.Message;
            }
            return result;
        }

        public DateTime GetDateGraficByLoanID(int mLoanID, int iterationID)
        {
            DateTime result = new DateTime();
            try
            {
                string query = " SELECT calculatedData "
                    + " FROM LoanOrders "
                    + " WHERE LoanID = " + mLoanID
                    + " AND iterationID = " + iterationID
                    + " AND finishedTranche = 0 "
                    + " ";

                DataTable tempResult = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (tempResult != null && tempResult.Rows.Count == 1 && tempResult.Rows[0][0] != System.DBNull.Value)
                {
                    result = (DateTime)tempResult.Rows[0][0];
                }
            }
            catch (Exception ex)
            {
                mLastError += "Error into GetIterationByLoanID function. " + ex.Message;
            }
            return result;
        }

        public decimal GetProfitByLoanID(int mLoanID, int iteration)
        {
            decimal result = 0;
            try
            {
                string query = " SELECT percentValue "
                    + " FROM LoanOrders "
                    + " WHERE LoanID = " + mLoanID
                    + " AND iterationID = " + iteration
                    + " AND finishedTranche = 0 "
                    + " ";

                DataTable tempResult = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (tempResult != null && tempResult.Rows.Count == 1 && tempResult.Rows[0][0] != System.DBNull.Value)
                {
                    string percentValue = tempResult.Rows[0][0].ToString();
                    decimal.TryParse(percentValue, out result);
                }
            }
            catch (Exception ex)
            {
                mLastError += "Error into GetIterationByLoanID function. " + ex.Message;
            }
            return result;
        }

        public decimal GetMainBYLoanID(int mLoanID, int iteration)
        {
            decimal result = 0;
            try
            {
                string query = " SELECT mainRate "
                    + " FROM LoanOrders "
                    + " WHERE LoanID = " + mLoanID
                    + " AND iterationID = " + iteration
                    + " AND finishedTranche = 0 "
                    + " ";

                DataTable tempResult = mDataBridge.ExecuteQuery(query);
                mLastError = mDataBridge.LastError;

                if (tempResult != null && tempResult.Rows.Count == 1 && tempResult.Rows[0][0] != System.DBNull.Value)
                {
                    string mainRate = tempResult.Rows[0][0].ToString();
                    decimal.TryParse(mainRate, out result);
                }
            }
            catch (Exception ex)
            {
                mLastError += "Error into GetIterationByLoanID function. " + ex.Message;
            }
            return result;
        }
    }

    namespace Domains
    {
        /// <summary>
        /// Default domain. InputClientPersonalData.
        /// </summary>
        public class InputClientPersonalData
        {
            public static readonly string Name = "Input Contraget Personal Data";
            public static readonly string Description = "Allow View/Edit Input Contragent Personal Data";

            public static void Register()
            {
                Registrar.RegisterDomain(Module.ID, Name, Description);
            }
        }

        /// <summary>
        /// Default domain. ManageCredits.
        /// </summary>
        public class ManageLoans
        {
            public static readonly string Name = "Manage Loans";
            public static readonly string Description = "Allow View/Edit loans";

            public static void Register()
            {
                Registrar.RegisterDomain(Module.ID, Name, Description);
            }
        }

        /// <summary>
        /// Default domain.  Approval Credits.
        /// </summary>
        public class ApprovalLoans
        {
            public static readonly string Name = "Approval Loans";
            public static readonly string Description = "Allow Approve/Decline Loans";

            public static void Register()
            {
                Registrar.RegisterDomain(Module.ID, Name, Description);
            }
        }

        /// <summary>
        /// Default domain. ManageLoanProducts.
        /// </summary>
        public class ManageLoanProducts
        {
            public static readonly string Name = "Manage Loan Products";
            public static readonly string Description = "Allow View/Edit loan products";

            public static void Register()
            {
                Registrar.RegisterDomain(Module.ID, Name, Description);
            }
        }

        /// <summary>
        /// Default domain. ManageLoanProducts.
        /// </summary>
        public class ManageConsultEval
        {
            public static readonly string Name = "Manage Evaluation and Counseling Process";
            public static readonly string Description = "Allow View/Edit evaluation and counseling clients";

            public static void Register()
            {
                Registrar.RegisterDomain(Module.ID, Name, Description);
            }
        }


        /// <summary>
        /// Reports
        /// </summary>
        public class Reports
        {
            public static readonly string Name = "Reports";
            public static readonly string Description = "Allow generate general reports";

            public static void Register()
            {
                Registrar.RegisterDomain(Module.ID, Name, Description);
            }
        }
    }
}
